Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I'm working with my first attempt to create a useful PowerQuery connection towards REST API (Github - https://developer.github.com/v3/#pagination). Unfortunately I have no "from scratch" M-coding experience which seems needed here (nor can the API developer help me with PQ).
I'm able to authenticate and connect to API successfully, however PQ only exports 100 records. Pagination seems to be the magic word. I wouldn't even want to paginate through all records since that would fetch needlessly old data. While it would be cool to paginate until a certain dynamic date (TODAY - x days), I also expect to be fine to paginate a fixed amount of pages which will give more or less the same result. E.g. 5 pages / 500 records at all times.
Guidance from the developer is that in JSON header, there is a link to next 100-record page (as well as previous in case it's not the 1st), however I don't see how I can make PQ use that to append several pages together. All I see is JSON body in PowerQuery, although through browser, sure, header and the working next /prev page link is available.
HTTP 200 OK
Allow: GET, POST, HEAD, OPTIONS
Content-Type: application/json
Link:
<https://my.apiprovider.com/tasks/?cursor=MYURLHASH=-created_at>; rel="next",
<https://my.apiprovider.com/tasks/?cursor=MYURLHASH&ordering=-created_at>; rel="prev" 'only available from 2nd page onwards
Vary: Accept
[
The PowerQuery-automated M code is also very brief - doesn't even have authentication details, which I guess is handled outside of M. I assume pagination should happen within it?
let
Source = Json.Document(Web.Contents("https://my.apiprovider.com/tasks/?ordering=-created_at")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "url", "account", "state", "created_at", "assignee"})
in
#"Expanded Column1"
How should I solve this? Thanks a lot in advance for any feedback!
Please look at the documentation for your REST API, and hopefully you can use $top, $filter, and/or $skip in your web call. I prefer to use $skip over pagination when available. You can use $filter to limit the returns to your date range of interest, and $top may be able to be used to increase the # of rows returned (100 may be the default but limit may be much higher). If that isn't enough rows, you can use $skip to go to the next set of rows with each subsequent web call. And you can generate the list of numbers to concatenate into those web calls in your query with List.Numbers( ).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.