Forum Discussion
Reading one "Blank Query" script by multiple reports, and still being able to refresh
- Anonymous9 years ago
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"- amadeusw9 years agoNew 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)
- Anonymous9 years agoNot applicable
I created a "suggestion" for this. Please upvote.