Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

Paginación de una API de REST en Power Query mediante M

Buenos días a todos,

Con la esperanza de que pueda ayudar, ya que estoy bien y verdaderamente atascado. Me he conectado correctamente a una API rest usando el siguiente código:

let

    AuthKey = "Token",
    url = "https://psa.pulseway.com/api/?$orderby=OpenDate desc",

    Source = Json.Document(Web.Contents(url,[
             Headers = [Authorization="Bearer " & AuthKey]
             ,RelativePath = "servicedesk/tickets/"])),
    Result = Source[Result],
    #"Converted to Table" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
    #"Converted to Table"

.. sin embargo, esto sólo devuelve los primeros 100 resultados de aproximadamente 6500 filas esperadas. Algunas lecturas me dicen que necesito paginar mis resultados indexando primero cuántas páginas de resultados hay y combinarlas una a la vez.

Esto me llevó a este artículo donde había tomado prestado, ammended y probado el código:

let 
    BaseUrl         = "https://psa.pulseway.com/api/servicedesk/tickets",
    Token           = "TOKEN",
    EntitiesPerPage = 100,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

... no tiene errores de sintaxis, sin embargo, no funciona y obtengo el siguiente error:

Capture.JPG

Sé que la URL y el token de acceso funcionan como lo había probado con mi primer bloque de código, y sé que la API admite el uso de la $count $top y $skip parámetros de consulta como lo dice en la documentación...

EXPRESIONES DE FILTRO, ORDENACIÓN Y PAGINACIÓN

Cuando una solicitud GET devuelve varios registros, las expresiones de Odata se pueden anexar al final del URI. Los tres tipos de expresiones determinan si los varios registros se filtran, se ordenan o se paginan. La documentación identifica si un campo se puede filtrar u ordenar. Las expresiones de paginación siempre están disponibles para cualquier método GET que devuelva varios registros. Se pueden combinar varias expresiones de Odata en el mismo URI. En el ejemplo siguiente, la primera expresión de Odata está delimitada por un carácter de pregunta (?). Las expresiones de Odata subsiguientes se delimitan con un carácter de y comercial (&).

GET /api/accounts?$skip=30&$top=10&$orderby=Nombre

Si alguien puede decirme dónde me estoy equivocando, sería muy apreciado, ya que simplemente no puedo entender esto.

Si bien sería ideal y preferido, no necesariamente necesito todos los registros de 6500 +.

Si fuera más fácil quitar la parte del código que examina cuántos registros o páginas, entonces podría ser:

  • Las primeras 10 páginas (100 por página x 10 = 1000)
  • Todo dentro de los 12 meses de una columna llamada "OpenDate"

Gracias de antemano y espero sus respuestas.

8 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    Después de pasar todo el día en esto, me he dado cuenta de que el código anterior no funcionará en absoluto, ya que la API no enumera el número de páginas a las que debo llamar. Con eso en mente, he logrado llegar al número de páginas de esta manera:

    let 
    
    Token = "Token",
    BaseURL = "https://psa.pulseway.com/api/",
    Path = "servicedesk/tickets/",
    RecordsPerPage = 100,
 
    //the below line returns the total number of records - currently 6594
    CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),
             TotalRecords = CountTickets[TotalRecords],

    //This line divides the number of records by the number of records per page to determine the total number of pages - Currently 66
    PageCount = Number.RoundUp(TotalRecords / RecordsPerPage),

    Todavía no puedo averiguar el código que llamará a cada una de las 66 páginas por separado y luego las combinará todas juntas. Esto es lo que tengo:

    let 
    
    Token = "Token",
    BaseURL = "https://psa.pulseway.com/api/",
    Path = "servicedesk/tickets/",
    RecordsPerPage = 100,
 
    //the below line returns the total number of records - currently 6594
    CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),
             TotalRecords = CountTickets[TotalRecords],

    //This line divides the total number of records by the number of records per page to determine the total number of pages - Currently 66
    PageCount = Number.RoundUp(TotalRecords / RecordsPerPage),
    
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Path & Skip & "&" & Top,
            Json  = GetJson(URL),
            Value = Json[#"value"]
        in  Value,

    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
    Table

    Si alguien puede señalarme en la dirección correcta, lo agradeceríamos mucho.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    5 years ago

    Allí, @jwillis07

    Puede consultar estos documentos y comprobar si pueden ayudarle:

    https://docs.microsoft.com/en-us/power-query/handlingpaging

    https://stackoverflow.com/questions/66888658/paging-rest-api-results-in-power-query

    https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi

    https://stackoverflow.com/questions/63920538/issue-fetching-paginated-data-from-a-rest-api-in-power-bi-with-power-query

    Saludos

    Equipo de apoyo a la comunidad _Robert Qin

    Si este post ayuda,entonces por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      2 years ago

      Propósito: cargue los datos paginados de la API, que cargan solo 50 filas por página. Esta api tiene 2 parámetros de URL:

      un. limit: Número de filas que se van a cargar por página

      b. skip: Omite el número de filas desde el principio. 0 - omitir ninguno; 50 - Omita 50 y comience la página desde la fila 51.

      y un token de autorización (portador).

      Pasos:

      1. Invoque la API base con encabezados (opcional), haciendo clic en las opciones del menú, Obtener datos -> Web ->Avanzado

      Si la llamada se realiza correctamente, PowerBI abrirá el Editor de Power Query y cargará los resultados en una tabla

      2. En el panel de consulta izquierdo, haga clic en el nombre de la consulta

      3. Haga clic en Editor avanzado en el menú superior.

      4. Seleccione todo y copie el contenido en el portapapeles/Bloc de notas.

      5. Haga clic en Cancelar para salir del cuadro de diálogo del editor.

      6. Elimine esta consulta haciendo clic con el botón derecho en el nombre de la consulta y haciendo clic en la opción Eliminar .

      7. Para crear una nueva consulta, haga clic con el botón derecho en cualquier lugar del panel izquierdo (Consulta) para abrir el menú contextual y seleccione Nueva consulta > consulta en blanco

      8. Haga clic en Editor avanzado en el menú superior.

      9. Seleccione todo y borre el código de plantilla predeterminado.

      10. Pega el contenido copiado en el Paso #4. Dependiendo de la transformación, las entradas pueden tener un aspecto similar al siguiente.

      11. Haga esto como una función, que se puede invocar varias veces para manejar la paginación, agregando el parámetro de función. Agregue una línea al principio como se muestra a continuación.

      Tenga en cuenta.
      a. el parámetro de la primera línea
      b. Compruebe el área resaltada de la url. Límite del parámetro Url = 50. En mi caso, el parámetro url indica que la API tiene que obtener 50 filas por página/llamada. El parámetro de URL y los límites pueden ser diferentes
      c. El parámetro de desplazamiento de función se agrega al parámetro url skip para omitir el número de filas desde el inicio de los datos completos. Usamos Number.ToText para convertir el número en texto para concatular con la cadena url.
      12. Asigne un nombre a la función añadiéndola a la entrada. Manipularemos la salida de la función (que se muestra a continuación como puntos de puntos) en los pasos siguientes

      13. Dado que la función está lista ahora, el siguiente paso es
      un. Llame a esta función en un bucle para obtener todos los datos, página por página.

      b. Determinar cuándo detener el bucle cuando se recuperan todos los datos

      c. Combine todos los resultados en una tabla.

      Para obtener el número total de filas que se van a capturar
      Para obtener el total de filas en el conjunto de datos de la API, podemos llamar y almacenar el resultado de la llamada a la API base y luego usar su atributo total .

      aquí allRows es la variable donde estoy almacenando el resultado de la llamada a la API base.

      Bucle hasta que se recuperen todas las filas

      List.Generate nos ayudará a hacer un bucle y llamar a la función tantas veces como sea necesario. En mi caso, el límite de páginas es 50 y, por lo tanto, el bucle se configura en incrementos de 50 hasta que se obtienen todas las filas, lo que está determinado por el atributo allRows[total].

      apiCallResults crea una lista de tablas, como resultado de nuestras múltiples llamadas realizadas por el bucle List.Generate .

      Para combinar todos los resultados en una tabla

      Table.Combine(tabla1, tabla2 ..) nos ayudará a fusionar todas las tablas de resultados. Por lo tanto, vamos a combinar la tabla a nuestra salida.

      Como tenemos todo aquí, reemplazamos las líneas con puntos con este bloque de entrada como se muestra a continuación.

      14. Este es el ensamblaje final

      Referencias:

      https://learn.microsoft.com/en-us/powerquery-m/web-contents

      https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi

      https://gorilla.bi/power-query/list-generate-api-calls/

      https://learn.microsoft.com/en-us/powerquery-m/table-combine