Forum Discussion
Pagination of a REST API in Power Query using M
- 5 years ago
Hi, jwillis07
You can refer to these documents and check if they can help:
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
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
After spending all day on this I've realised the above code will not work at all as the API doesn't list the number of pages for me to call. With that in mind, I've managed to get to the number of pages this way:
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),
I still can't figure out the code that will call each of the 66 pages seperately and then combine them all together. This is as far as I've got:
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
If anyone can point me in the right direction it would most appreciated!
Hi!
I was looking for a solution and came up with this brilliant way.
https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi
Check it out!
- ericknishimoto4 years agoNew Member
Thx 👍👍👍