Forum Discussion
Refresh published report in service
Hi all,
I have a report created in Power BI Desktop that loads a few JSON files from a private github repo.
That all works well.
However, I'd love to somehow have this report refresh automatically (on a schedule), so that it picks up changes made to those JSON files.
It seems like the JSON and Web connectors don't support data refresh though. I believe that this means that I will, daily, have to refresh the data on my desktop and then publish the new report.
Is there no other, better, way to achieve an up to date report?
Thanks,
David
8 Replies
Hi david_obrien1 ,
there is a workaround that might work for you:
(1) Workaround - Power BI - Web content refresh - Data Gateway | LinkedInThat doesn't seem to help.
Here's the query I'm already using.
let json = Json.Document(Web.Contents("https://raw.githubusercontent.com/davidobrien1985/repoName/file.json")), #"Converted to Table" = Table.FromRecords({json}), #"Expanded history" = Table.ExpandListColumn(#"Converted to Table", "history"), #"Expanded history1" = Table.ExpandRecordColumn(#"Expanded history", "history", {"numberOfRoles", "numberOfOperations", "date"}, {"history.numberOfRoles", "history.numberOfOperations", "history.date"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded history1",{{"numberOfRoles", Int64.Type}, {"numberOfOperations", Int64.Type}, {"history.numberOfRoles", Int64.Type}, {"history.numberOfOperations", Int64.Type}, {"history.date", type date}}) in #"Changed Type"The repo that hosts the json file is private, but it's on the internet. The query works. The only thing it doesn't want to do is automatically refresh (on a schedule).
Hi david_obrien1 ,
do you get an error-message (and if so: what does it say?) or is the scheduled refresh simply greyed out?
I am a bit surprised that the query works without without an API key if it sits in a private repo, tbh.I don't get an error message and can't configure data refresh because I don't even know where to configure this.
If I go to File -> Options and Settings -> Data source settings I only see this.
I found this article here (Skip Test Connection for On-premises and Cloud Data Sources | Microsoft Power BI Blog | Microsoft Power BI) and my Power BI Desktop looks completely different. I don't see any of those dialogues.
The query works using Basic authentication. I do provide the GitHub PAT.
Hi david_obrien1 ,
you cannot configure this in PBI Desktop, but must do it in the service. So publish you report to the service and then follow the instructions here: Configure scheduled refresh - Power BI | Microsoft Learnah okay, when checking there it looks like this, which kind of brings me back to my original post.
It seems like JSON / Web queries do not support automatic / scheduled refresh.
I'd be interested in a workaround for that. Surely there has to be a way to achieve this.
I even tried just clicking "refresh now" in the service or refreshing the dataset via REST API and 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.
Hi david_obrien1 ,
I don't know where you got that from, but there is absolutely no limitation of web refresh with regards to the JSON format. Any limitation must come from something else.
If you google problems with dynamic datasources Power Query refreshes you find a couple of hints and workarounds.
It's probably due to the fact that your query is different to what you have posted above.It's literally in the error message that says that the query doesn't support this.
Or what does that error message mean?
There is a table on learn.microsoft.com that also shows all the connectors and says that web and JSON don't support refresh.
The published query is identical to the one I posted. Not sure why you'd think it's different.
I'm confused that the error says it's not possible and you saying it is possible.
There is a table on learn.microsoft.com (which I can't find right now, I'm on the phone) that also shows all the connectors and says that web and JSON don't support refresh.
