Forum Discussion
Scheduled Refresh for Web.Content
Dear Community,
I've been trying to publish a dataset on the https://app.powerbi.com service.
I'm stuck with the refreshing feature though. There is my workflow :
I create a query on Desktop, running API calls :
let url = "https://platform.gaelenlighten.com/api/reports?FormName=Safety%20Incident%20Investigation%20Report&&ReportStatus=All&&&&&&&&&&&IsMandatory=All&&&&&&&&OrderBy=Default&OrderByDesc=Default&ThenBy=Default&ThenByDesc=Default&&&key=apikey", Options = [Headers=[ #"Authorization" = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",#"Tenant" = "xxxxxxxx.gaelenlighten.com"]],
iterations = 1000, FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents(url, Options)), data = try Source[Reports] otherwise null, next = try Source[Links]{1}[RequestUrl] otherwise null, total = try Source[TotalResults] otherwise null, res = [Data=data, Next=next, Total=total] in res, GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [res][Total] > 0 and [res][Data]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [res][Data]) in GeneratedList
This works fine in PBI Desktop.
When publishing, the refresh does not work on the app.powerbi.com : Web.Content is not supported.
From there, I use the workaround explained here : https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/
And modify the code to get :
let
url = "https://platform.gaelenlighten.com/api/reports?FormName=Safety%20Incident%20Investigation%20Report&&ReportStatus=All&&&&&&&&&&&IsMandatory=All&&&&&&&&OrderBy=Default&OrderByDesc=Default&ThenBy=Default&ThenByDesc=Default&&",
Options = [Headers=[ #"Authorization" = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",#"Tenant" = "xxxxxxx.gaelenlighten.com"]],
iterations = 1000,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://platform.gaelenlighten.com/api/reports?FormName=Safety%20Incident%20Investigation%20Report&&ReportStatus=All&&&&&&&&&&&IsMandatory=All&&&&&&&&OrderBy=Default&OrderByDesc=Default&ThenBy=Default&ThenByDesc=Default&&", Options)),
data = try Source[Reports] otherwise null,
next = try Source[Links]{1}[RequestUrl] otherwise null,
total = try Source[TotalResults] otherwise null,
res = [Data=data, Next=next, Total=total]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [res][Total] > 0 and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])
in
GeneratedListThe refresh online are not showing error apparently.
Thought, when I access my dataset, i do get :
"Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again."
The credential type is anonymous, which is the one I use in desktop. Not working here though, while perfectly working in desktop.
I've tried different types, but couldn't make this work.
Any support would be much appreciated !
Thank you,
Nicolas
1 Reply
- v-xicaiCommunity Support
Hi NicolasMKEO ,
1.Please try to upgrade the Power BI Desktop to the latest version, then republish again.
2.Your data source credential may expire, so try to update the credential and republish the report. In Power BI, go to refresh settings for the dataset. In Manage Data Sources, select Edit credentials to update the credentials for the data source.
3.If your data source contains local data source, then you may need to install and configure the latest data gateway. See more :On-premises data gateway .
4.Mashups between a cloud source and an on-premises source, in a single query, fail to refresh in the gateway (personal mode) if one of the sources is using OAuth for authentication. An example of this issue is a mashup between CRM Online and a local SQL Server instance. The mashup fails because CRM Online requires OAuth. To work around the problem, have a separate query for the cloud source and the on-premises source. Then, use a merge or append query to combine them.
If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support , Scroll down and click "CREATE SUPPORT TICKET", to get further help.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.