Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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?

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        amadeusw
        New Member

        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)