Forum Discussion
how to create a query that paginates?
I am looking for a pagination solution too, and I am almost there but not quite....!
here is my code (and it feels very close):
let
baseURL = "https://api.vworkapp.com/v4/jobs.xml",
apiKey = "?api_key=xxxxxxxx",
params = "&start_at=2018-10-15&end_at=2018-10-16",
perPage = "&per_page=200",
queryURL = baseURL & apiKey & params & perPage,
PreFetchData = Xml.Tables(Web.Contents(queryURL)),
PageCount = Number.FromText(PreFetchData{0}[#"Attribute:total_pages"]),
GetPageData = (Index) =>
let
XMLData = Xml.Tables(Web.Contents(queryURL & "&page=" & Index))
in
XMLData,
Data = List.Generate ( () =>
[i=1],
each [i] < PageCount,
each [
i=[i]+1,
ResList = GetPageData(
Number.ToText([i])
)
],
each [ResList]
)
in
Data What this gets me is:
The Table records are spot on, I can expand those and they are exactly what I want, but there is an error in the first list item and I can't figure out why:
I can't seem to get past this error, can anyone put me on the right track to solving why i=1 would be a problem? The api accepts page=1 so I don't see what would be wrong....
EDIT: I have skipped over that row and everything else after that works as expected, so if I solve why i=1 is an issue then I am done!
Anonymous looks to me that you are making it too complex...i think , in your case, u can paginate simply by calling a function. See this page
http://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response.html
- PBI_KLo7 years ago
Helper I
Thank you Reddy. That was the most elegant solution I found in my use case and worked like a charm.