Forum Discussion

hanuraolm's avatar
hanuraolm
Helper I
3 years ago

PowerQuery Post API call to return complete data from all pages.

A search of the frontend application returned 21891 records using 219 pages. Using PowerQuery's POST API, we are trying to call this data

PowerQuery - 

3 - Quries

Tool Utilization Query  - below is the code I'm using to return the results and it's returning only first 100 records. How to loop this code to get complete 21819 records from 219 pages.

let
    apiUrl = "https://abc.net/dfd/v1/unrestricted-context-search?userId=rtr",

    bearerToken = getbearerToken(),
    options = [
        Content = Text.ToBinary(requestBody),
        Headers = [
            #"Content-Type"="application/json"
    

        ]
    ],
    apiResponse = Web.Contents(apiUrl, options),
#"Imported JSON" = Json.Document(apiResponse,1252),
docs = #"Imported JSON"[docs]


in
#"Expanded Column1

 

Need Help

8 Replies

  • ams1's avatar
    ams1
    Responsive Resident
    3 years ago

    Hi


    Looping is done using List.Generate.

     

    I recently answered some questions related to paging:

     

    If you cannot manage to fix it using the above links, please reply and I'll have a look at your code.

     

    Also it will be important to know where is the next page token/number in the API response and how to request the next page.

     

    Please mark this as answer if it helped.

    • hanuraolm's avatar
      hanuraolm
      Helper I
      3 years ago

      i could see Authorization: Bearer , kvAccessToken is same for all the pages.

      when i click on the next page --

      {"query":"((end_time ge '2023-01-18T05:28:31Z' and start_time le '2023-02-17T05:28:31Z') and (file_type_name eq 'datalog'))","fields":[],"count":true,"start":100,"limit":100,"sort":{"fieldname":"end_time","order":"desc"}}

      Next page ----

      {"query":"((end_time ge '2023-01-18T05:30:02Z' and start_time le '2023-02-17T05:30:02Z') and (file_type_name eq 'datalog'))","fields":[],"count":true,"start":200,"limit":100,"sort":{"fieldname":"end_time","order":"desc"}}

       

      seems "start" is the changed every time, then "limit":100 every time.

       

      these days are same for all page, because i choose last 30 days in application...

      end_time ge '2023-01-18T05:28:31Z' and start_time le '2023-02-17T05:28:31Z'

       

      • hanuraolm's avatar
        hanuraolm
        Helper I
        3 years ago

        Your help in sharing the code structure would be greatly appreciated.

  • hanuraolm's avatar
    hanuraolm
    Helper I
    3 years ago

     

    Let's assume that in the particular search criteria my API returns 60,000 records and my API call will always allow 1000 records. Due to the sequential pagination logic, it took longer to load the entire data set. In the example attached, I can read the complete data. Let's say we have 60000 records and each API call will support 1000 records, so the total number of pages is 60000/1000=60 pages. If each 1000 records took 4 seconds to load, then it will take 240 seconds to load all the data. How can the dataload be made fast through parallel mechanisms.

    • ams1's avatar
      ams1
      Responsive Resident
      3 years ago

      Hi hanuraolm ,

       

      I see the problem.

       

      There is also a similar post regaring this: https://community.powerbi.com/t5/Power-Query/Power-Query-Parallel-or-Concurrent-REST-Invocation/td-p/2106491

       

      Unfortunately there it states that "Parellel operations are not permitted within a single query" - not sure if it's still actual.

       

      I would still try below potential solution (adding a column with starts and a second column to get the data), BUT if that doesn't work, as suggested also by the OP of the above question, I'd create an intermediery (local python, custom software hosted in a service etc.) that would make the requests in paralell and make them accessible to PowerQuery somehow.

       

      Below potential solution ğŸ˜Š

      Given that you know the start of all pages immediately after you run the first request, one option could be to FIRST create a column with the start of all pages, like this:

      and THEN add a column that invokes getPageData using the above start column instead of List.Generate - maybe this will help:

       

      The code to do this could look like:

          ... your code

    maxPages = Number.RoundUp(totalRecords/1000),
    pagesStartList = List.Generate(
        () => 0,
        (lastPage) => lastPage < maxPages,
        (lastPage) => lastPage + 1,
        (lastPage) => lastPage * 1000
    ),
    pagesStartTable = Table.FromColumns({pagesStartList}, {"start"}),
    // Define a function to retrieve data for a single page
    getPageData = (start as number) =>
        let
            ... your code
        in
            data,

    #"Added Custom" = Table.AddColumn(pagesStartTable, "pageData", each getPageData([start]))
in 
    #"Added Custom"

       

      I haven't tested it, but if it works, please do tell us if it did AND also mark this as ANSWER if it helped.