Need help with iterating through API response pages

[tom_malkiewicz]

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"

[mahoneypat]

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

[tom_malkiewicz]

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

[mahoneypat]

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

[tom_malkiewicz]

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"

[lbendlin]

Please refer to my earlier reply.  You need to modify your process to avoid that.

[tom_malkiewicz]

Hey, 

Apologies I thought I did. I replaced my version with static page with your version of getPage function.

[lbendlin]

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)

[lbendlin]

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.

[lbendlin]

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.