Dynamic Data Sources and API

Question

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 · Answer

You need to change your code to use RelativePath and Query
&nbsp;
Web.Contents - PowerQuery M | Microsoft Learn
&nbsp;
&nbsp;

dpccgf · Answer

Hey, I can give this a shot but am under a small time crunch. &nbsp;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. &nbsp;They are just against other json tables that pull in 100 records per page. &nbsp;TIA

lbendlin · Answer

Please check the example 1 at the link I included. It is straightforward.
&nbsp;
As you can probably appreciate it is nearly impossible to help with API queries without access to said API&nbsp; (which you may not be willing to provide for understandable reasons)

dpccgf · Answer

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

dpccgf · Answer

Ibendlin, so I got the below query to work.&nbsp; How can I tell if it is actually getting 100 records at a time vs. all 4,167 records at once?&nbsp; 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.&nbsp; TIA&nbsp;let#"BaseURL" = "https://XXXX.XXXXXX.com/api/v1/",queryHeaders = [#"Content-Type" = "application/json",#"Authorization" = "Token XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"],Source = List.Generate(()=&gt; [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"&nbsp;

Forum Discussion

Dynamic Data Sources and API

9 Replies

Related content

Convert Dynamic Data source

Data source

Dynamic data source issue

Bit of help to identifying a dynamic data source

Data Source Credentials