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.
I'm having some issue refreshing using the Power Query function Web.Contents().
I need to pull data from an API which limits to 100 results per page. I created a custom function that is able to iterate through pages until the next page is empty.
GetPage = (url, path) =>
let Json = GetJson(url, path),
TestUrl = Text.From(Json[#"paging"][#"next"]),
UrlEnd = Text.Range(TestUrl, n),
Value = Json[#"data"],
response = if TestUrl <> null then List.Combine({Value, @GetPage(BaseUrl, UrlEnd)}) else Value
in response,
GetJson = (Url, Path) =>
let Options = [Headers=[ #"Authorization" = "Bearer " & Token ], RelativePath = Path],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
When I conduct the refresh in Power BI Desktop I'm able to do the refresh. But when I publish the report to Power BI Server I get the following error:
"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources."
I went through a few tutorials on how to fix this issue:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
https://community.powerbi.com/t5/Service/Dynamic-Web-Contents-Power-BI-Refresh-Error/m-p/714526
All pages give the recommendation of using the RelativePath. I started using this as evident from code above, but it still raises the same error. For my base url I have tried https://eu.bill.dotyk.cloud/nua-barcelona/.
The url path is then:
Orders?IncludeItems=false&IncludeTransactions=false&api-version=2020-01&CreatedAfterTimeSpan=-6.00%3A00%3A00
I realize there is a query parameter in Web.Contents() which I have tried. But this did not work.
Any input would be great.
Venia
Solved! Go to Solution.
Thank you! I was actually a little confused how to put api-version in the query because of the hyphen.
The solution was acutally a little different from what you recommend. What I ended up doing was passing in the URL directly, not into a function. Now the function looks like
GetJson = (offssett) =>
let Options =
[
Headers=[ #"Authorization" = "Bearer " & Token ],
Query =
[
IncludeItems="true",
IncludeTransactions="true",
Limit="100",
Offset=offssett,
CreatedAfter= Text.From(RangeStart)
]
],
RawData = Web.Contents("https://eu.bill.dotyk.cloud/nua-barcelona/Orders?api-version=2020-06-01", Options),
Json = Json.Document(RawData)
in Json,
Json = GetJson(BaseUrl, UrlPath),
Value = Json[#"data"],
GetPage = (offssett) =>
let Json = GetJson(Text.From(offssett)),
TestUrl = if Json[#"paging"] <> null then Text.From(Json[#"paging"][#"next"]) else null,
Value = Json[#"data"],
response = if TestUrl <> null then List.Combine({Value, @GetPage(offssett + 100)}) else Value
in response,
But now I'll also fix the api-version.
Thank you! I was actually a little confused how to put api-version in the query because of the hyphen.
The solution was acutally a little different from what you recommend. What I ended up doing was passing in the URL directly, not into a function. Now the function looks like
GetJson = (offssett) =>
let Options =
[
Headers=[ #"Authorization" = "Bearer " & Token ],
Query =
[
IncludeItems="true",
IncludeTransactions="true",
Limit="100",
Offset=offssett,
CreatedAfter= Text.From(RangeStart)
]
],
RawData = Web.Contents("https://eu.bill.dotyk.cloud/nua-barcelona/Orders?api-version=2020-06-01", Options),
Json = Json.Document(RawData)
in Json,
Json = GetJson(BaseUrl, UrlPath),
Value = Json[#"data"],
GetPage = (offssett) =>
let Json = GetJson(Text.From(offssett)),
TestUrl = if Json[#"paging"] <> null then Text.From(Json[#"paging"][#"next"]) else null,
Value = Json[#"data"],
response = if TestUrl <> null then List.Combine({Value, @GetPage(offssett + 100)}) else Value
in response,
But now I'll also fix the api-version.
You're nearly there. Your relative path should be "orders" but everything else (everything after the question mark) needs to go in the "query" portion.
Something like this
let
Source = Json.Document(Web.Contents("https://eu.bill.dotyk.cloud/nua-barcelona",[RelativePath="Orders",Query=[IncludeItems="false",IncludeTransactions="false",#"api-version"="2020-01",CreatedAfterTimeSpan="-6.00:00:00"]]))
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.
User | Count |
---|---|
113 | |
97 | |
85 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |