¿Cómo crear una consulta que pagina?

Con respecto a los errores por encima de 1500, puede inyectar un controlador de errores como este:

CallFunction = Table.AddColumn(Source, "CallFunction", each try Function(Text.From([Start]),Text.From([Finish])) otherwise #table({"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {})),

Esto devuelve una tabla en blanco con los nombres de sus columnas en caso de que la llamada web falle.

Los retornos nulos pueden deberse a nombres de campo incorrectos en la expansión del registro. Podrías probar esto:

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Record.FieldNames(#"Converted to Table"[Column1]{0}) )

esto expandirá todos los campos que tienen los mismos nombres de campo que el registro de la primera fila

¡Hola!

Después de leer la mayor parte del hilo y un par de horas de intentos y errores más tarde, la frustración y el bloqueo mental comienzan a afectarme.

Estoy tratando de adaptar las muestras de código para solicitar datos de Airtable.com, que usa la misma paginación con un parámetro de compensación como en el último ejemplo. ¿Quizás alguien pueda ayudarme?

Para facilitar la reproducción, creé una base de datos de muestra con cinco entradas.

Ejemplo de cómo funciona la API, estoy solicitando solo dos registros (con el parámetro pageSize = 2) para permitir la prueba con datos mínimos …

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

El parámetro offset se puede insertar en la siguiente llamada, para obtener los siguientes dos registros:

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

Para solicitar el conjunto de datos completo (sin paginación), realizaría lo siguiente en Power BI

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

El caso es que el parámetro de compensación debe ser válido y no puede dejarse vacío. Para la primera API-Call, no debe enviarse en absoluto.

Intenté reunir esta codificación y la codificación de ejemplo de antes:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"records", "offset"}, {"Column1.records", "Column1.offset"}),
    #"Erweiterte Column1.records" = Table.ExpandListColumn(#"Erweiterte Column1", "Column1.records"),
    #"Erweiterte Column1.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.records", "Column1.records", {"id", "fields", "createdTime"}, {"Column1.records.id", "Column1.records.fields", "Column1.records.createdTime"})
in
    #"Erweiterte Column1.records1"

La cosa es ahora, que la primera llamada a la API parece ejecutarse dos veces, al menos obtengo los dos primeros valores duplicados en el conjunto de resultados.

¿Hay alguna forma de «inicializar» Last_Key y solo pasar el parámetro offset si Last_Key no está inicializado?

Probé esto:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "&init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else "&offset="[WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2"&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),

Lo cual enviaría un parámetro «init» vacío en la primera llamada (a la API no le importa eso), y concatenaría el parámetro offset más tarde. Pero esto solo devuelve las dos primeras entradas dos veces.

¿Alguien puede ayudarme? ¡¡Muchísimas gracias!!

Atentamente

Moritz

Es tarde, así que solo una idea rápida ahora y tal vez más mañana 🙂

Tienes que poner una condición en el paso «WebCall», como:

si contador = 0 entonces … CallWithoutOffset / LastKey else YourCurrentString

Hola Imke,

gracias por su respuesta. ¡Eso funcionó! Y el Contador <= 1 tuvo que cambiarse a Contador <1 ... ¡Muchas gracias!

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>&pageSize=2")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
    #"Erweiterte Column1.Value" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.Value", {"records", "offset"}, {"Column1.Value.records", "Column1.Value.offset"}),
    #"Erweiterte Column1.Value.records" = Table.ExpandListColumn(#"Erweiterte Column1.Value", "Column1.Value.records"),
    #"Erweiterte Column1.Value.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records", "Column1.Value.records", {"id", "fields", "createdTime"}, {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),
    #"Erweiterte Column1.Value.records.fields" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records1", "Column1.Value.records.fields", {"Condition", "Priority", "Room"}, {"Column1.Value.records.fields.Condition", "Column1.Value.records.fields.Priority", "Column1.Value.records.fields.Room"})
in
    #"Erweiterte Column1.Value.records.fields"

Hola a todos,

Solo quería compartir un código que escribí para obtener datos de la búsqueda elástica usando su escaneo y paginación de desplazamiento.

Entonces, si tiene muchos datos en la búsqueda elástica, debe obtenerlos en lotes, y este código hace exactamente eso, tal vez sea útil para alguien:

let
FnGetElasticData = (url as text) as list =>
let
	// init params
	size=10000, // the size of the "chunks" of data that will be returned on each batch
	scroll = "1m", // the time in minutes elastic search should keep the "context" alive
	// build url for first fetch
	operator = if Text.Contains(url, "?") then "&" else "?",
	initUrl = url & operator & "scroll=" & scroll & "&size=" & Text.From(size),
	
	// get first batch of results
	initSource = Json.Document(Web.Contents(initUrl, [IsRetry=true])),
	totalResults = initSource[hits][total], // the total number of results to return
	iterations = Number.IntegerDivide(totalResults, size), // the total number of iterations we need to do
	
	// build url for scroll
	scrollId = initSource[_scroll_id], // the scroll id with wich we will fetch the rest of the results
	uriParts = Uri.Parts(url), // for getting the host and scheme of the url
	scrollUrl = uriParts[Scheme] & "://" & uriParts[Host] & "/_search/scroll?scroll=" & scroll & "&scroll_id=" & scrollId,
	
	// this will return a scrolled result from elastic search
	FnGetScrolledPage = (url as text) as list =>
		let
			response = Json.Document(Web.Contents(url, [IsRetry=true])),
			data = response[hits][hits]
		in
			data,
	
	// now loop through all of the iterations and return the data
	resultsList = List.Generate(()=>[i=0, res=initSource[hits][hits]], // Set inital data
		each [i] <= iterations, // Keep going until all of the iterations have been done or there is no more data
		each [i=[i] + 1, res = FnGetScrolledPage(scrollUrl)], // Get next batch of results		
		each [res])
in
    resultsList
in
FnGetElasticData

Básicamente, creará una función a la que puede llamar con la URL de su consulta de elasticsearch y devolverá todos los resultados mediante paginación.

Tenga en cuenta que este código no funcionará en las actualizaciones automáticas con Power BI Gateway porque espera URL estáticas, puede superar esto con bastante facilidad cambiando el código anterior para incluir las URL codificadas y luego la actualización programada funcionará.

Hola Mike,

si la URL de la página siguiente se ve así:

http: //example.co.uk/example/feedback-details? from = 2016-01-01% 2009: 00: 00 & to = 2017-05-03% 2009: 30: 00 & pa …,

todo lo que tienes que hacer es crear una tabla con una fila por llamada necesaria:

Table.FromColumns({{1..300000/100}})

Agregue otra columna donde haga referencia a la primera columna como parámetro / variable a su llamada web:

Web.Page(Web.Contents("http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1])))

No importa en qué formato se devolverá el resultado. Cree una función que recupere uno de ellos y aplíquelo como paso siguiente a cada fila (resultados devueltos en cada).

Hola,

¿Existe la posibilidad de que el valor máximo («300000») se tome de una acción anterior o una variable que tenga como objetivo tener toda la serie «1..300000 / 100» codificada?

La idea sería ejecutar una llamada para obtener el máximo y luego crear la tabla e iterar las llamadas.

Saludos.

Si. Actualmente estoy trabajando en una publicación de blog para describir exactamente esto. Publicará linke una vez terminado.

lmkeF,

Muchas gracias por ser un recurso tan bueno, ¡realmente lo apreciamos!

Estoy en una situación difícil … armé un proyecto para un cliente que extrae datos de Airtable a través de una API en Excel. He terminado por completo con el proyecto, ¡excepto que acabo de descubrir que Airtable solo puede extraer 100 registros a la vez! He estado leyendo estas publicaciones, pero parece que todavía no puedo resolver el problema de la paginación. Actualmente hay casi 1.000 registros y el número de registros crecerá. Pido disculpas, soy un poco novato escribiendo código en este idioma.

Copié y pegué su código directamente en mi consulta, pero recibo un error (ver más abajo). ¿Qué estoy haciendo mal? Se supone que debo enviarle esto al cliente mañana por la mañana, ¡así que espero que lo veas pronto (cruzando los dedos)! ¡Gracias nuevamente por su experiencia!

ERROR: Expression.SyntaxError: Token Comma esperado. (Ver código en rojo a continuación)

let
LINK = "https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE",
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0 , // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents(LINK],Query=[page=Text.From([Pages])]])),
// retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = try Table.FromRecords(WebCall[events]) otherwise Table.FromList({}) // steps of your further query
                      ]   ,
                each [Table] 		
), 1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Hola @nerd_in_NE

A primera vista, estoy bastante seguro de que hay algún problema con su enlace, ya que el argumento de la página no está en ninguna parte.

Los contenidos de la web La fila debería verse así (resaltada en rojo):

each [ WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE&page=1",Query=[page=Text.From([Pages])]])),

Sin embargo, tenga en cuenta que el formato depende en gran medida de la API a la que está llamando. (es decir, ¿cómo llamaría a la primera página en la API? ¿Es ‘page’ incluso el argumento que necesitaría o se llama de alguna otra manera?)

Además, la siguiente parte de la consulta también depende de su API:

Table = try Table.FromRecords(WebCall[events])

Más específicamente, el [events] el nombre dependerá del nombre del registro que está extrayendo (puede ser cualquier cosa, dependiendo de su api)

DBa,

Muchas gracias por la pronta respuesta !!!

De hecho, pude resolver mis problemas utilizando una solución publicada por «Mo_re» en la publicación a continuación.

https: //community.airtable.com/t/json-string-only-contains-100-rows-when-connected-to-power-bi-deskt …

Para mayor comodidad, aquí está el código:

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1)
in
    Pagination

¿Lo está utilizando dentro de un comando «Table.AddColumn»?

En caso afirmativo, comparta el código de consulta completo

let
    Source = Table.FromColumns({{1..500/100}}),
    GetData = Table.AddColumn(Source, "New", each Xml.Tables(Web.Contents("http://example.co.uk/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1]))))

in
    GetData

Lo ha codificado a mano: el uso de la interfaz de usuario le habría dado los valores adicionales 😉

¡Hola!

Después de leer la mayor parte del hilo y un par de horas de intentos y errores más tarde, la frustración y el bloqueo mental comienzan a afectarme.

Estoy tratando de adaptar las muestras de código para solicitar datos de Airtable.com, que usa la misma paginación con un parámetro de compensación como en el último ejemplo. ¿Quizás alguien pueda ayudarme?

Para facilitar la reproducción, creé una base de datos de muestra con cinco entradas.

Ejemplo de cómo funciona la API, estoy solicitando solo dos registros (con el parámetro pageSize = 2) para permitir la prueba con datos mínimos …

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

El parámetro offset se puede insertar en la siguiente llamada, para obtener los siguientes dos registros:

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

Para solicitar el conjunto de datos completo (sin paginación), realizaría lo siguiente en Power BI

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

Ahora traté de reunir esta codificación y la codificación de ejemplo de antes:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1)
, #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
, #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"})
, #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
  #"Expanded Column1.fields"

Pero recibo un error, la columna «Columna1» de la tabla no se pudo encontrar. Parece que mi «Paginación» ya está vacía.

Editar: Parece que los errores surgen porque el parámetro de compensación no puede estar vacío en la primera llamada y solo se puede completar con valores válidos; de lo contrario, la llamada a la API fallará. Quité el y [Last_Key] <> «» de la tercera fila y ahora finalmente obtengo datos (¡hurra!) pero dos registros se recuperan dos veces, así que en lugar de cinco entradas, devuelve siete. ¿Hay alguna forma de que pueda inicializar el «Last_Key» y solo pasarlo al parámetro de compensación cuando no es inicial?

¿Alguien puede ayudarme? ¡¡Muchísimas gracias!!

Atentamente

Moritz