Forum Discussion
Helper IVDynamic Data Sources and API
Super UserPlease 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)
Helper IVIbendlin, 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