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 GetData(1) otherwise null, Page =1],
each [Result] <> null,
each [Result = try GetData([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
