Forum Discussion
Need help with iterating through API response pages
Hello,
I have been trying to find a solution for the past week now and still struggling.
I have an access to an API of our CRM system. The api response comes with total number of records per entity.There is no next link in the response. I can specify a number of records per page between 100 and 200. I have followed several posts from here as well as Youtube videos based on Matt Masson's blog post from 2014, unsuccesfully 😞
Here is my query. It's acopy of the query from one of the posts here. I am getting first 100 responses multiplied by number of pages. I know it's something small and obvious but it's just been too long... please help 🙂
let
BaseUrl = "https://xxxxxxxxxxxxxxxxxxxxxxxxxx.entities/c_nominal_code",
Token = "TOKEN",
EntitiesPerPage = 100,
GetJson = (Url) =>
let Options = [Headers=[#"Content-Type"="application/json", #"Beacon-Application"="developer_api", Authorization="Bearer " & Token]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url),
Count = Json[total]
in Count,
GetPage = (Index) =>
let Url = BaseUrl & "?Page=1",
Json = GetJson(Url),
Value = Json[#"results"]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
#"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"entity"}, {"Column1.entity"})
in
#"Expanded Column2"
9 Replies
The standard approach is:
- fetch (and buffer) the first page.
- get the total record count from that
- either generate a list of the URLS for the second to nth page or run an iterator if you have a next page indicator
- concatenate the first page results with the results from the previous step. This avoids having to re-fetch the first page again.
Looks like you've hard coded ?Page=1 in your function, so each iteration in the list will get the same records I'm guessing. You need to pass another parameter in the function, to accept the iterated value of your list. Please try it with this change.
GetPage = (Index) =>
let Url = BaseUrl & "?Page=" & Number.ToText(Index),
Json = GetJson(Url),
Value = Json[#"results"]
in Value,FYI that this video shows a different way to do it without uses pages, if you can use Skip or Offset, in case it helps.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
Hi mahoneypat ,
Thanks fro this. Yes I know I had it harcoded just wasn'tsure how to use the total pages as number of iterations. And you're right if I could use offset and skip that would be easier. The only values this API is providing is Total and I can set the Per_page value to either 100 or 200.
Tom
Have you already tried adapting this function like this?
GetPage = (Index) =>
let Url = BaseUrl & "?Page=" & Number.ToText(Index),
Json = GetJson(Url),
Value = Json[#"results"]
in Value,I also don't understand your List.Max line, but if it works, great.
Pat
