I came across Access Analytic's November 2021 Power Query Challenge on Wyn Hopkins' LinkedIn feed.
Here's the file with my PQ answer.
What I found intriguing about this challange was that at first glance, it appeared something that would benefit from a few rounds of unpivoting and transposing - Leila Gharani has an excellent video on the subject.
But then in the spirit of challanges, I decided to challange myself not to transpose the data.
I also used #date within #date with Date.Daysinmonth to achieve the result.
Happy spreadsheeting and power query-ing!
The M-code is printed below for your convenience:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Bottom Rows" = Table.RemoveLastN(Source,1),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Bottom Rows", {"Column1", "Column2"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Element", each if [Attribute]="Finance" or [Attribute]="Operations" or [Attribute]= "HR" then "Actual" else if [Attribute]="Column3" or [Attribute]="Column5" or [Attribute]="Column7" then "Budget" else "Variance"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Department", each if Text.Contains ([Attribute], "Column") then null else [Attribute]),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Department"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Attribute"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Removed Columns", "Column2", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column2.1", "Column2.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Character Transition",{{"Column2.1", "Case"}, {"Column2.2", "Year"}, {"Column1", "Month"}}),
#"Removed Top Rows" = Table.Skip(#"Renamed Columns",9),
#"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","Case"," Case",Replacer.ReplaceText,{"Case"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Year", Int64.Type}, {"Month", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Date", each #date([Year], [Month], Date.DaysInMonth (#date([Year], [Month],1)))),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Month", "Year"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Case]), "Case", "Value", List.Sum),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Element] <> "Variance")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Department", "Element", "Date", "Base Case", "Worst Case"})
in
#"Reordered Columns"Feel free to download the challange file with my solution below:
PQ-November-2021-Challenge-2.xlsx
Cheers,
Abdullah.