Welcome to powertothemasses.com.
M-code for Calendar:
let
Source = List.Dates(Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))-4,1,1,0,0,0)), Duration.Days(Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))+10,1,1,0,0,0))-Date.From(#datetime(Date.Year(DateTime.Date(DateTime.LocalNow()))-4,1,1,0,0,0))), #duration(1, 0, 0, 0)),
#"Converted to Table1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month", each Date.MonthName([Date]), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month", each Text.Start(_, 3), type text}}),
#"Inserted Month" = Table.AddColumn(#"Extracted First Characters", "Month#", each Date.Month([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Week", "Week of Year", each if [Day of Week] = 7 then Date.WeekOfYear([Date]) + 1 else Date.WeekOfYear([Date])),
#"Added Custom" = Table.AddColumn(#"Inserted Week of Year", "Yearmonth", each [Year]*100+[#"Month#"]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Yearmonth", Int64.Type}}),
Reference1 = Table.ReorderColumns(#"Changed Type1",{"Date", "Year", "Month", "Month#", "Yearmonth", "Day of Week", "Week of Year"}),
#"Removed Other Columns" = Table.SelectColumns(Reference1,{"Yearmonth"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "YMIndex", 0, 1),
#"Merged Queries" = Table.NestedJoin(Reference1,{"Yearmonth"},#"Added Index",{"Yearmonth"},"Added Index",JoinKind.LeftOuter),
#"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"YMIndex"}, {"YMIndex"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Added Index",{{"YMIndex", Int64.Type}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Date", "Year", "Month", "Month#", "Yearmonth", "YMIndex", "Day of Week", "Week of Year"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns1", "Year", "Year - Copy"),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Year - Copy", type text}}, "en-AU"), "Year - Copy", Splitter.SplitTextByPositions({0, 2}, false), {"Year - Copy.1", "Year - Copy.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position",{{"Year - Copy.1", Int64.Type}, {"Year - Copy.2", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Year - Copy.2", "Shortyear"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [#"Month#"] <=6 then [#"Shortyear"] - 1 else [#"Shortyear"]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each if [#"Month#"] >=7 then [Shortyear] +1 else [Shortyear]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Shortyear"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.1", type text}, {"Custom", type text}, {"Year - Copy.1", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type4", "FinYear", each [#"Year - Copy.1"]&[Custom]&"/"&[Custom.1]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"FinYear", type text}}),
Reference2 = Table.RemoveColumns(#"Changed Type5",{"Year - Copy.1", "Custom", "Custom.1"}),
#"Removed Other Columns1" = Table.SelectColumns(Reference2,{"FinYear"}),
#"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"),
#"Added Index1" = Table.AddIndexColumn(#"Removed Duplicates1", "FYIndex", 0, 1),
#"Merged Queries1" = Table.NestedJoin(#"Reference2",{"FinYear"},#"Added Index1",{"FinYear"},"Added Index1",JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index1", {"FYIndex"}, {"FYIndex"}),
#"Inserted End of Week" = Table.AddColumn(#"Expanded Added Index1", "Previous Friday", each Date.AddDays(Date.EndOfWeek([Date]),-2-7), type date),
#"Duplicated Column1" = Table.DuplicateColumn(#"Inserted End of Week", "Month", "FinMonth"),
#"Added Custom4" = Table.AddColumn(#"Duplicated Column1", "FinMonth#", each if [#"Month#"] >= 7 then [#"Month#"] -6 else [#"Month#"] +6),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom4",{{"FinMonth#", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type6",{"Date", "Year", "Month", "Month#", "Yearmonth", "YMIndex", "Day of Week", "Week of Year", "FinYear", "FinMonth", "FinMonth#", "FYIndex", "Previous Friday"}),
#"Inserted Quarter" = Table.AddColumn(#"Reordered Columns", "YQtr", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom5" = Table.AddColumn(#"Inserted Quarter", "Custom", each [Year]*10+[YQtr]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom5",{"YQtr"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "YearQtr"}}),
Reference3 = Table.TransformColumnTypes(#"Renamed Columns2",{{"YearQtr", Int64.Type}}),
#"Removed Other Columns2" = Table.SelectColumns(Reference3,{"YearQtr"}),
#"Removed Duplicates2" = Table.Distinct(#"Removed Other Columns2"),
#"Added Index2" = Table.AddIndexColumn(#"Removed Duplicates2", "YQIndex", 0, 1),
#"Merged Queries2" = Table.NestedJoin(#"Reference3",{"YearQtr"},#"Added Index2",{"YearQtr"},"Reference3",JoinKind.LeftOuter),
#"Expanded Reference3" = Table.ExpandTableColumn(#"Merged Queries2", "Reference3", {"YQIndex"}, {"YQIndex"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Reference3",{{"Week of Year", "Week#"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Week#", Int64.Type}})
in
#"Changed Type7"
Sign up to become a member of the Hobart Power BI User Group HERE.
Enjoy building your Power BI model!
