I have a custom Power Query script that generates a big and wide calendar table. Besides static dates, it has columns that comunicate information relative to a current date (e.g. difference between the date in the table and current date, YTD or not YTD, and so on). Therefore, the script has to be re-run with each refresh in order to update all values.
At first, I had same script in multiple reports. Which was a pain if I had to make a change - I had to update each script.
So, I found a workaround. I placed the script into a text file, and read off that file, which is placed on the web:
Source = Text.FromBinary(Web.Contents("https://********-my.sharepoint.com/personal/*****_********_com/Documents/Power BI/Data/SourceCalendar.txt")),
Evaluate = Expression.Evaluate(Source, #shared)
Now, this works great!
But now, I schedule a refresh! I get this:
"You can't schedule refresh for this dataset because one or more sources currently don't support refresh."
What are other ways to make this work?
I guess I could write up an SQL script, and put it on Azure? I'm not a developer, so I'm not sure how to do it exactly.
I tried putting the script into an Excel table, and reading off that Excel table placed on the cloud, but then the data isn't up to date until I "refresh" the data in Excel. Which is also a pain.
How can I read off a custom script by multiple report, or maybe what is another solution to my issue?
I'm also interested in this question. Currently we manually update queries from OneNote. We would like to store custom queries on git / file share and ultimately have PowerBI update automatically (or by clicking the Refresh button)