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.
Hi all,
I am relatively new to using Power BI and have been running into an issue when trying to call a lot of data from my database public REST API. Currently I am doing a separate query for each month but would like to find a way to loop the query to iterate through each month.
Each row of data has a unique identifier so could iterate through that way? The API has a standard return of 200 rows but you can specify limit in the URL up to 10,000. The API does not have an offset available so a lot of options for this I see online are not viable. The API also returns the data as a List of Records.
I have tried to create a recursive loop to get this working as follows using a blank query (User Key and Secret are set as parameters):
let
fetchData = (url, headers) =>
let
response = Json.Document(Web.Contents(url, headers)),
data = response[Data]
in
data,
url = "my_API_URL",
headers = [
#"User" = User,
#"Key" = Key,
#"Secret" = Secret
],
result = List.Generate(
() => [url = url, headers = headers, data = {}],
each List.Count([data]) > 0,
each [url = url, headers = headers, data = fetchData([url], [headers])],
each [url = url, headers = headers, data = {}]
),
allData = List.Combine(List.Transform(result, each [data]))
in
allData
This doesn't return any errors but is not producing any results so not sure where I am going wrong. Any additions to the current function or another method to loop the query would be greatly appreciated 🙂
Hi, @Fatau2130 regardless of anything else, this is what stops List.Generate to produce any result.
() => [url = url, headers = headers, data = {}],
each List.Count([data]) > 0
Your first item has zero values in data list.
How are you passing the filter for each month into the API? Also, if your API doesn't have skip or offset, does it offer pagination and gives you a link to the next set of results?
Pat
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.