Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
I created a "suggestion" for this. Please upvote.
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
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"
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)
I created a "suggestion" for this. Please upvote.