Hobart Power BI User Group

 

Welcome to powertothemasses.com.

Power Pivot intro

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!