cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
taraskaduk Advocate I
Advocate I

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
taraskaduk Advocate I
Advocate I

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

4 REPLIES 4
Moderator v-qiuyu-msft
Moderator

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

Hi @taraskaduk,

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
taraskaduk Advocate I
Advocate I

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

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
New Member

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

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)

taraskaduk Advocate I
Advocate I

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors