Composite Solution to Podcast 2316 Challenge

February 25, 2020 - by Bill Jelen

After studying all of the ideas sent from viewers, I have chosen my favorite techniques from each video. My final solution uses these steps:

• Get Data, From Named Range
• Delete the two extra steps added to Promote Headers and Change Type. This prevents having to break the suffix from the quarters. Thanks to Jason M, Ondřej Malinský, and Peter Bartholomew for this idea.
• Transpose
• Promote Headers
• Remove, Top Rows, Top 5 Rows. Nice trick from MF Wong.
• Replace Q1 with _Q1. Repeat for other three quarters. Thanks Jonathan Cooper.
• Split by Delimiter at the _. This amazing step keeps the names in one column and moves the quarters to the next column. Proposed by Fowmy, perfected by Jonathan Cooper.
• (Not a step!) Reach into the Formula Bar and rename the columns to be Employee and Quarter. Thanks Josh Johnson
• In the Employee column, replace nothing with null
• Fill Down
• In the Quarter column, change null to Total. This idea from Michael Karpfen
• Unpivot Other Columns. Rename Attrib to Category in the formula bar
• Pivot Quarters
• Move Total Column to the End

Here is my final code:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category Description", type text}, {"Administrative", type number}, {"Holiday", Int64.Type}, {"PTO/LOA/Jury Duty", Int64.Type}, {"Project A", type number}, {"Project B", type number}, {"Project C", type number}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","Q1","_Q1",Replacer.ReplaceText,{"Category Description"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Q2","_Q2",Replacer.ReplaceText,{"Category Description"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Q3","_Q3",Replacer.ReplaceText,{"Category Description"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Q4","_Q4",Replacer.ReplaceText,{"Category Description"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3", "Category Description", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Employee", "Qtr"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Employee", type text}, {"Qtr", type text}}),
    #"Replaced Value4" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"Employee"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value4",{"Employee"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Filled Down",null,"Total",Replacer.ReplaceValue,{"Qtr"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value5", {"Employee", "Qtr"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Qtr]), "Qtr", "Value", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Employee", "Attribute", "Q1", "Q2", "Q3", "Q4", "Total"})
in
    #"Reordered Columns"

Everyone mentioned in these articles or video wins an Excel Guru patch. I’ve already mailed several out. If you don’t receive one, leave a comment on the video below.

The overall winner is Bill Szysz. His four-line solution using M tells me that I need to learn a lot more about Power Query! See his solutions at Power Query: The World of Bill Szysz.

Watch Video

Here is my final video discussing the solutions and showing the final solution.

Return to the main page for the Podcast 2316 challenge.

Title Photo: Photo by Perry Grone on Unsplash