Dynamic Data Sources and API

[DPCCGF]

Hello! I am trying to figure out how to modify the below code so that I can refresh in the PowerBI service without the Dynamic Data Source error. Getting 100 rows at a time and just paging for new records. It works on the desktop. I have read several articles, blogs and this forum but for the life of me cannot figure out what to do. Can anyone help? Thanks so much!

let Source = Json.Document(Web.Contents("https://XXXX.XXXXX.com/api/v1/" & "XXXXXXXXX.json" & "?_limit=100", [Headers=[#"Content-Type"="application/json", Authorization="Token xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])), totalPagesOrganizationPeople = (Source[count]/100), CreateList = List.Numbers(0,totalPagesOrganizationPeople,100), #"Converted to Table" = Table.FromList(CreateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://XXXX.XXXXX.com/api/v1/" & "organizations.json" & "?_limit=100" & "&_offset=" &[Column1], [Headers=[#"Content-Type"="application/json", Authorization="Token XXXXXXXXXXXXXXXXXXXXXXXXXXXX"]]))), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"results"}, {"Custom.results"}), #"Expanded Custom.results" = Table.ExpandListColumn(#"Expanded Custom", "Custom.results"), #"Expanded Custom.results1" = Table.ExpandRecordColumn(#"Expanded Custom.results", "Custom.results", {"url", "id", "organization", "person", "groups", "data", "alternate_id", "admin_notes", "documents", "status", "myuserpermissions", "assigned_programs", "created", "created_by", "modified", "modified_by"}, {"results.url", "results.id", "results.organization", "results.person", "results.groups", "results.data", "results.alternate_id", "results.admin_notes", "results.documents", "results.status", "results.myuserpermissions", "results.assigned_programs", "results.created", "results.created_by", "results.modified", "results.modified_by"}) in #"Expanded Custom.results1"

[lbendlin]

You need to change your code to use RelativePath and Query

Web.Contents - PowerQuery M | Microsoft Learn

[DPCCGF]

Hey, I can give this a shot but am under a small time crunch.  If you want to make a little holiday money feel free to quote me to give me an example that I can use and apply to other similar API queries I have.  They are just against other json tables that pull in 100 records per page.  TIA

[lbendlin]

Please check the example 1 at the link I included. It is straightforward.

As you can probably appreciate it is nearly impossible to help with API queries without access to said API  (which you may not be willing to provide for understandable reasons)

[DPCCGF]

Ibendlin, so I got the below query to work.  How can I tell if it is actually getting 100 records at a time vs. all 4,167 records at once?  I have a Column called Column1.pageoffset that starts at 0 and then after 100 records goes to 100 then after another one hundred goes to 200 and so forth.  TIA

let
#"BaseURL" = "https://XXXX.XXXXXX.com/api/v1/",
queryHeaders = [
#"Content-Type" = "application/json",
#"Authorization" = "Token XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
],
Source = List.Generate(
()=> [
pageOffset = 0,
getOrgPeople = Json.Document(
Web.Contents(
#"BaseURL",
[
RelativePath = "organizationpeople.json?",
Query =
[
_limit = "100",
_offset = Number.ToText(pageOffset)
],
Headers = queryHeaders
]
)
)[results]
],
each List.IsEmpty([getOrgPeople]) = false,
each [
pageOffset = [pageOffset]+100,
getOrgPeople = Json.Document(
Web.Contents(
#"BaseURL",
[
RelativePath = "organizationpeople.json?",
Query =
[
_limit = "100",
_offset = Number.ToText(pageOffset)
],
Headers = queryHeaders
]
)
)[results]
]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"pageOffset", "getOrgPeople"}, {"Column1.pageOffset", "Column1.getOrgPeople"}),
#"Expanded Column1.getOrgPeople" = Table.ExpandListColumn(#"Expanded Column1", "Column1.getOrgPeople"),
#"Expanded Column1.getOrgPeople1" = Table.ExpandRecordColumn(#"Expanded Column1.getOrgPeople", "Column1.getOrgPeople", {"person"}, {"Column1.getOrgPeople.person"})
in
#"Expanded Column1.getOrgPeople1"

[lbendlin]

The question mark is not part of the RelativePath - you can omit it. Power Query will automatically add it if you specify query parameters.

Speaking of which - yours have leading underscores. Is that how it is defined in the API documentation? Rather unusual.

[DPCCGF]

Yes, when querying through their API site you will page 100 records at a time, and the URL will be something like the below and then you will just toggle through pages until you hit the end.  So outside of that is it actually getting 100 records at a time instead of pulling the whole set of data in one fell swoop?  Thank you so much for your help!

https://acme.training.com/api/v1/organizationpeople.json?_limit=100&_offset=100

[lbendlin]

Fair enough. So you can use multiple approaches for paging - here is one I like (even comes with its own little helper function )

Handling paging for Power Query connectors - Power Query | Microsoft Learn

[DPCCGF]

Thanks!  I will check this out.  This is my first "foray" into getting data via an API and my guess is it will not be my last.  So I really appreciate your help.  For now it appears that I am paging though correct?  If so I will leave as is as I have a data model to change quickly, but will begin testing out your suggestion next week.

[DPCCGF]

I will check it out.  Thank you!  Do you mind then if I pop into this thread if u have a few questions here or there?