Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DPCCGF
Helper II
Helper II

Dynamic Data Sources and API

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"

9 REPLIES 9
lbendlin
Super User
Super User

You need to change your code to use RelativePath and Query

 

Web.Contents - PowerQuery M | Microsoft Learn

 

 

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

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)

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"

 

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.

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

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

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.

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.