Forum Discussion
Automatic pagination with incrementing offset
- Anonymous5 years ago
Please review the following blog to implement REST API pagination in Power Query.
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Please review the following blog to implement REST API pagination in Power Query.
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Thanks! Even though the article did not help for my exact problem I could use it as a start to build my own functions. Since my API does not show my the maximum entries with one request I had to build a function which call itself until the point where my itemcount is below 100 which means that I reached my final page. After lots of trial and error it seems to work now. I'd like to share my code and get some feedback if I did everything right.
let
BaseUrl = "xx",
Token = "xxx",
Offset = 0,
GetJson = (Url) =>
let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = (x) =>
let Url = BaseUrl & Text.From(x),
Json = GetJson(Url),
Count = Json[#"itemCount"]
in Count,
GetPage = (x) =>
let Offset = Text.From(Offset + x),
Url = BaseUrl & Offset,
Json = GetJson(Url),
Value = Json[#"data"]
in Value,
CheckCount = (x as number) =>
let Indices =
if GetEntityCount(x) < 100
then x
else @CheckCount (x+100)
in Indices,
PageIndices = { 0 .. CheckCount(0)/100 },
Pages = List.Transform(PageIndices, each GetPage(_*100)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
So the return is the exact 1698 Projects which is up to the offset=1700 endpoint. I am just wondering why I am also getting all projects when I start with:
PageIndices = { 0 .. CheckCount(1500)/100 }
Shouldn't it just return 298 Projects then since I start with offset 1500?
Best regards!