Forum Discussion
Need help with iterating through API response pages
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
List.Max is not doing anything as EntitiesPerPage is a constant, so it will always return that. Maybe in some other scenarios it would be useful, akin to COALESCE.
Yes I did. So the entity I am pulling as a test has 2 pages and I am getting first page twice.
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=" & Number.ToText(Index),
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"}),
#"Expanded Column1.entity" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.entity", {"id", "created_at", "created_by_id", "created_by_type", "updated_at", "updated_by_id", "updated_by_type", "is_archived", "archived_at", "archived_by_id", "archived_by_type", "avatar", "entity_type_id", "c_name", "c_notes", "c_code", "c_payments_made_this_year", "c_payments_made_last_year", "c_sort_code", "c_account_number", "c_type", "c_summary", "c_sun_journal_account_description"}, {"Column1.entity.id", "Column1.entity.created_at", "Column1.entity.created_by_id", "Column1.entity.created_by_type", "Column1.entity.updated_at", "Column1.entity.updated_by_id", "Column1.entity.updated_by_type", "Column1.entity.is_archived", "Column1.entity.archived_at", "Column1.entity.archived_by_id", "Column1.entity.archived_by_type", "Column1.entity.avatar", "Column1.entity.entity_type_id", "Column1.entity.c_name", "Column1.entity.c_notes", "Column1.entity.c_code", "Column1.entity.c_payments_made_this_year", "Column1.entity.c_payments_made_last_year", "Column1.entity.c_sort_code", "Column1.entity.c_account_number", "Column1.entity.c_type", "Column1.entity.c_summary", "Column1.entity.c_sun_journal_account_description"}),
#"Sorted Rows" = Table.Sort(#"Expanded Column1.entity",{{"Column1.entity.id", Order.Ascending}})
in
#"Sorted Rows"Please refer to my earlier reply. You need to modify your process to avoid that.
