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 guys, I am using the below power query code for getting the data from an API. I am using a loop to get the data for many rows as the API can provide onpy 5000 rows for a call. But I am not able to apply data refresh. I even tried using Relative path as suggested by few people, but it's still not working. I cannot provide the API key as it is confidential. Also, keep in mind that I do not want to use personal gateway. Thank you in advance.
let
#"FullQuery" = let
pageSize = 5000,
sendRequest = (page) =>
let
APIKey = "test",
APIUrl = "https://api.genscape.com/",
startDate = Date.ToText(Date.AddMonths(Date.From(DateTimeZone.UtcNow()),-11),"yyyy-MM-dd"),
Response = Web.Contents(
"https://api.genscape.com/",
[
RelativePath ="storage/oil/v1/tank-volumes",
Query=
[
revision="published",
format="json",
startDate=startDate,
limit = Number.ToText(pageSize),
offset = Number.ToText(page * pageSize)
],
Headers=[#"Gen-Api-Key"= APIKey]
]),
Data = Json.Document(Response)[data]
in
Data,
Loop = (page as number, AccumData as list) =>
let
Data = Function.InvokeAfter(()=>sendRequest(page), #duration(0,0,0,2)),
Result =
if List.Count(Data) < pageSize
then Table.FromRecords(List.Combine({AccumData, Data}))
else @Loop(page + 1, List.Combine({AccumData, Data}))
in
Result
in
Loop(0, {})
in
#"FullQuery"
I am getting the below error when I try to schedule refresh.
Solved! Go to Solution.
Sorry I hadn't read your post correctly. What you need is a base URL that actually exists on the service.
https://api.genscape.com/ is not valid (the server refuses to serve that page).
Select "skip test" and ignore the error.
"But I am not able to apply data refresh"
Please elaborate. What error message did you get?
Hi lbendlin,
I am getting the below error message.
Thank you,
Yash
Sorry I hadn't read your post correctly. What you need is a base URL that actually exists on the service.
https://api.genscape.com/ is not valid (the server refuses to serve that page).
Select "skip test" and ignore the error.
Can you successfully fetch a single page? The first ten records? The error message indicates that the URL is incorrect.
Yes, I can fetch a single page when I use the below way to load data instead of using query to load many pages using loop.
Hi @Yash48 ,
Try the measure provided by Chris to deal with 404 error from Web.Contents:
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi Kelly,
That article was not able to solve my problem. The api url is working fine when I use in PowerBI desktop, but not when published.
Thank you,
Yash
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.