Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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 Learn
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
ah 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 ,
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 Po...) 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 ,
there is a workaround that might work for you:
(1) Workaround - Power BI - Web content refresh - Data Gateway | LinkedIn
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
That 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).