I have a custom query which makes an API call to a site with an API key to retreive data. However, the dataset return its limited to 100 results. I have altered the query to always look for the "next" field on return and if it exsists also query the url provided in the next field until it no longer exisits to ensure the full dataset it extracted. The following code works for this:
= (baseuri as text) =>
let
headers = [Headers=[#"Content-Type"="application/json", Authorization="Token XXXXXXX"]],
initReq = Json.Document(Web.Contents(baseuri, headers)),
alerts = initReq[alerts],
// Define the recursive function to fetch data
gather = (data as list, uri as text) =>
let
// Get new offset from the current URI
newOffset = Json.Document(Web.Contents(uri, headers))[next],
// Build new URI using the original URI so we don't append offsets
newUri = newOffset,
// Get new request & data
newReq = Json.Document(Web.Contents(newUri, headers)),
newAlerts = newReq[alerts],
// Add new alerts to the rolling aggregate
combinedData = List.Combine({data, newAlerts}),
// If there's no next page of data, return the combined data. Otherwise, call @gather again to get more data
result = if newReq[next] = null then combinedData else @gather(combinedData, newUri)
in
result,
// Before we call gather(), check if it's necessary
outputList = if initReq[next] = null then alerts else gather(alerts, baseuri),
// Convert the list of records into a table
expand = Table.FromRecords(outputList)
in
expand
When i publish this to PowerBI online i am unable to refresh it from the service as it is a dymanic data source.
I then created the following code to make 1 query which when published allows me to extract the first 100 which when published allows me to refresh:
let
Source = Json.Document(Web.Contents("https://api.site.com",
[
RelativePath = "/1.0/alerts/",
Query = [
limit = "1000",
min_timestamp = "2022-10-01T15:14:01.000Z",
status = "closed"
],
Headers = [Authorization = "Token XXXXXXXX"]
]
))
in
Source
However, i need to be able to query the full dataset, the "next" field when returned provides the full URL needed to be queryied. I tried the following query but it keeps failing to authenticate as anomoyous due to mutliple references to headers i believe:
let
headers = [Headers = [Authorization = "Token XXXXXX"]],
Source = Json.Document(Web.Contents("https://api.site.com",
[
RelativePath = "/1.0/alerts/",
Query = [
limit = "1000",
min_timestamp = "2022-10-01T15:14:01.000Z",
status = "closed"
],
Headers=[Authorization = "Token XXXXXXX"]
]
)),
alerts = Source[alerts],
// Define the recursive function to fetch data
gather = (data as list, uri as text) =>
let
// Get the JSON response for the current URI
response = Json.Document(Web.Contents(uri, headers)),
// Check if the 'next' field exists in the JSON response
nextPage = response[next],
// If 'next' exists, use its value as the new URI; otherwise, return null
newUri = if nextPage <> null then nextPage else null,
// If there's no next page of data, return null
result = if newUri = null then null else
let
// Get the 'alerts' field from the response
newAlerts = response[alerts],
// Add new alerts to the rolling aggregate
combinedData = List.Combine({data, newAlerts})
in
// Call the recursive function again with the new URI
@gather(combinedData, newUri)
in
result,
// Before we call gather(), check if it's necessary
outputList = if Source[next] = null then alerts else gather(alerts,"https://api.site.com"),
// Convert the list of records into a table
expand = Table.FromRecords(outputList)
in
expand
Is there a way to create this query so it looks for all of the data and make it non-dymanic to allow PowerBI to auto-refresh it?
