Forum Discussion
Web.Contents Power Query Incremental Refresh Error
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
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.
2 Replies
- lbendlin
Super User
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"]])) 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.
