Forum Discussion
Need help with iterating through API response pages
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
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"
- lbendlin5 years agoSuper User
Please refer to my earlier reply. You need to modify your process to avoid that.
- tom_malkiewicz5 years agoHelper I
Hey,
Apologies I thought I did. I replaced my version with static page with your version of getPage function.
- lbendlin5 years agoSuper User
PageIndices = { 2 .. PageCount },
and then concatenate page 1 from the earlier GetEntityCount() step (that you would have to modify to get the payload as well)