Reading one "Blank Query" script by multiple reports, and still being able to refresh

[Anonymous]

Hello!

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:

let
    Source = Text.FromBinary(Web.Contents("https://********-my.sharepoint.com/personal/*****_********_com/Documents/Power BI/Data/SourceCalendar.txt")),
    Evaluate = Expression.Evaluate(Source, #shared)
in
    Evaluate

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?

[v-qiuyu-msft]

Hi @Anonymous,

It seems that you want to make the calendar table update automatically based on current date, right?

If that is a case, you can create a table in desktop use DAX like below:

=CALENDAR(<start_date>, today())

If I misunderstand the requirement, please correct me.

Best Regards,

Qiuyun Yu

[Anonymous]

Not exactly.

I have a LARGE table with dates and different date properties.

I am using this table across all of my reports. 

I make changes in the table pretty frequently.

When I make a change, I want this chage to be instantly reflected in every report without me going into 20+ reports and fix it.

Here is my latest version of the table, you can add it as "Blank Query" to see what it produces:

let
		finClose = 18,
		revClose = 15,
		today = Date.From(DateTime.FixedLocalNow()),
		Source = List.Dates,
		Invoke = Source(#date(2012, 1, 1), Duration.Days(#date(2020,12,31)-#date(2012,1,1))+1, #duration(1, 0, 0, 0)),
		ConvertToTable = Table.FromList(Invoke, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
		RenameColumns = Table.RenameColumns(ConvertToTable,{{"Column1", "Date"}}),
		SortDates = Table.Sort(RenameColumns,{{"Date", Order.Descending}}),
		Year = Table.AddColumn(SortDates, "Year", each Date.Year([Date])),
		QNum = Table.AddColumn(Year, "QNum", each Date.QuarterOfYear([Date])),
		YearQ = Table.AddColumn(QNum, "Year.Q", each Date.ToText([Date],"yyyy") & "." & Number.ToText([QNum]) ),
		QName = Table.TransformColumns(YearQ, {{"QNum", each "Q" & Text.From(_, "en-US"), type text}}),
		Month = Table.AddColumn(QName, "Month", each Date.ToText([Date],"MM")),
		MonthNum = Table.AddColumn(Month, "MonthNum", each Date.Month([Date])),
		MonthName = Table.AddColumn(MonthNum, "Month Name", each Date.ToText([Date],"MMM")),
		YearMonth = Table.AddColumn(MonthName, "Year.Month", each Date.ToText([Date],"yyyy"&"."&[Month])),
		MonthYear = Table.AddColumn(YearMonth, "Month'Year", each [Month Name]&"'"&Date.ToText([Date],"yy")),
		Week = Table.AddColumn(MonthYear, "Week", each Date.WeekOfYear([Date])),
		Day = Table.AddColumn(Week, "Day", each Date.Day([Date]), type number),
		YTDReal = Table.AddColumn(Day, "YTD_Real", each Date.IsInYearToDate([Date])),
		YearDiff = Table.AddColumn(YTDReal, "Year diff", each Date.Year(Date.AddMonths(today,-1))-Date.Year([Date])),
		YearDiffReal = Table.AddColumn(YearDiff, "Year diff real", each Date.Year(today)-Date.Year([Date])),
		MonthDiff = Table.AddColumn(YearDiffReal, "Month diff", each Date.Year(DateTime.FixedLocalNow())*12 + Date.Month(DateTime.FixedLocalNow()) - Date.Year([Date])*12 - Date.Month([Date])),
		YTD = Table.AddColumn(MonthDiff, "YTD", each if [Year] = Date.Year(Date.AddMonths(today,-1)) then true else false),
		QuarterDiff = Table.AddColumn(YTD, "Q diff", each Date.Year(DateTime.FixedLocalNow())*4 + Date.QuarterOfYear(DateTime.FixedLocalNow()) - Date.Year([Date])*4 - Date.QuarterOfYear([Date])),
		DayDiff = Table.AddColumn(QuarterDiff, "Day diff", each Number.From(Date.From(DateTime.FixedLocalNow())) - Number.From([Date])),

		MonthDiffClosed = Table.AddColumn(DayDiff, "Month diff Closed", each if Date.Day(DateTime.FixedLocalNow())>finClose then [Month diff]-1 else [Month diff]-2, Int64.Type),
		LastMonth = Table.AddColumn(MonthDiffClosed, "Last Month Closed", each	if [Month diff Closed] = 0 then true else false),
		FinClosed = Table.AddColumn(LastMonth, "Closed Final", each 
		if [Month diff]>=2 
		then true 
		else if [Month diff]=1 and Date.Day(DateTime.FixedLocalNow())>finClose 
		then true
		else false),	

		RevClosed = Table.AddColumn(FinClosed, "Closed Revenue", each 
		if [Month diff]>=2 
		then true 
		else if [Month diff]=1 and Date.Day(DateTime.FixedLocalNow())>revClose 
		then true
		else false),

		R12 = Table.AddColumn(RevClosed, "Rolling 12 Months", each 
		if Date.Day(DateTime.FixedLocalNow())>finClose and [Month diff] <= 12 and [Month diff] >= 1 then true 
		else if Date.Day(DateTime.FixedLocalNow())<=finClose and [Month diff] <= 13 and [Month diff] >= 2 then true 
		else false),

		YearWeek = Table.AddColumn(R12, "Year.Week", each Date.ToText([Date],"yyyy")  & "." &  Text.End("0" & Number.ToText([Week]), 2)),
		BeforeThisWeek = Table.AddColumn(YearWeek, "Before This Week", each if [Year diff real] > 0 then true else if [Year diff real]=0 and [Week] < Date.WeekOfYear(today) then true else false, type logical),
		#"Inserted Start of Week" = Table.AddColumn(BeforeThisWeek, "StartOfWeek", each Date.StartOfWeek([Date]), type date),
		#"Changed Type" = Table.TransformColumnTypes(#"Inserted Start of Week",{{"Date", type date}, {"Year", type text}, {"QNum", type text}, {"Year.Q", type text}, {"Month", type text}, {"MonthNum", Int64.Type}, {"Month Name", type text}, {"Year.Month", type text}, {"Month'Year", type text}, {"Day", Int64.Type}, {"YTD", type logical}, {"Last Month Closed", type logical}, {"Year diff", Int64.Type}, {"Month diff", Int64.Type}, {"Q diff", Int64.Type}, {"Day diff", Int64.Type}, {"Closed Final", type logical}, {"Year.Week", type text}, {"Closed Revenue", type logical}, {"Rolling 12 Months", type logical}, {"Week", Int64.Type}, {"YTD_Real", type logical}, {"Year diff real", Int64.Type}}),
		DayOfWeek = Table.AddColumn(#"Changed Type", "DayOfWeek", each Date.DayOfWeek([Date],1), Int64.Type),
		Weekday = Table.AddColumn(DayOfWeek, "Weekday", each if [DayOfWeek] < 5 then true else false, type logical),
		#"Added Year Lag" = Table.AddColumn(Weekday, "Year lag", each if [Year diff real] =0 then "Current Year"
		else if [Year diff real] =1 then "Prior Year"
		else if [Year diff real] > 0 then Number.ToText([Year diff real]) & " years ago"
		else null)
in
#"Added Year Lag"

[amadeusw]

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)

[Anonymous · Accepted solution]

I created a "suggestion" for this. Please upvote.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18352027-support-refresh-from-the...