Forum Discussion
Helper IVDynamic Data Sources and API
Helper IVHey, 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
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)
- 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?
- 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