locked
Query to Paginate Through Data RRS feed

  • Question

  • Hello all, 

    Full disclosure here, this is for a class, however it is my senior project and this is a program that the company plans on using after it is finished. The project is to create a dashboard displaying some filtered data that is valuable to the company, in this case about cars who have been sitting in one place for too long. Filtering through it is not really the issue here. They don't have this data going back to a database, so the best bet to get live data is to pull it in with an API. Unfortunately, all of my group and I do not have any experience with APIs, or pulling data using them. The maximum entries we can pull at a time is 100, while we need to pull multiple data sets that have anywhere between 500 to thousands of entries. I think our best solution is having a query to automatically going through the pages and collection them in a table. I found a video on YouTube, Pagination and DO/WHILE in Power BI/Power Query by The Power User, that seemed like a good solution to my problem. However, when I've tried to implement it into my query, through Power Bi, the query ran for a very long time and while it did collect the data as I needed, it was all in the first 5 results while the query went on to collect 1,000 empty records. I'm not quite sure where I went wrong in the code, although I believe I need a different way to check if there's data on the page. Here's the code, although it's essentially what's done in the video:

    my GetData:

    = (page as number) as list =>
    let
        Source = Json.Document(Web.Contents("https://secure.fleetio.com/api/v1/vehicles/?page=" & Number.ToText(page), 
        [Headers=[Authorization="this_is_an_api_key", #"Account-Token"="additional_authentication"]])),
        Data1 = Source{1}[Data],
        RemoveBottom = Table.RemoveLastN(Data1, 3)
    in
        Source

    my query:

    = List.Generate( ()=>
             [Result= try vehicles(1) otherwise null, Page =1],
           each [Result] <> null,
           each [Result = try vehicles([Page]+1) otherwise null, Page =[Page]+1],
           each [Result]
    )

    I feel like the answer might be super obvious, but this is my first time seeing this language and I'm a bit lost. I would greatly appreciate any help, or even tips to point me in the right direction would be amazing.

    Thanks

    Thursday, October 3, 2019 12:24 AM

Answers

  • Hi dreamer529. The List.Generate call will keep trying to pull additional pages until [Result] is null (due to the GetData code throwing an error). It looks like your GetData code is returning a [Result] for pages up to one thousand.

    Ehren

    Monday, October 7, 2019 7:28 PM

All replies

  • Quick note, in my query the 'vehicles' should be 'GetData'
    Thursday, October 3, 2019 12:26 AM
  • Hi dreamer529. The List.Generate call will keep trying to pull additional pages until [Result] is null (due to the GetData code throwing an error). It looks like your GetData code is returning a [Result] for pages up to one thousand.

    Ehren

    Monday, October 7, 2019 7:28 PM
  • Hi dreamer529

    in addition to what Ehren has said: It could be that you don't get a null in return, but empty objects (empty record or table). To check for an empty record, you'd have to rewrite the 2nd argument like so:

    each Record.FieldCount( [Result] ) <> 0 ,


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, October 26, 2019 6:41 AM