Power Query: Isolating the Rows that represent Quarters

February 25, 2020 - by Bill Jelen

Kudos to Fowmy for this solution. It won’t work in real life, but it works nicely with the fake data given.

There is a point in each solution where you need to figure out which rows contain employee names and which rows contain quarters.

Rather than using a conditional column, Fowmy split by delimiter, using Q as the delimiter. Check this out... all of the quarters are now in a new column.

In the above, before you can Fill Down, you need the blanks in Employee Name to say Null. Fowmy solves this with:

= Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Employee Name"})

Here is why this won’t work with real data: There is going to be an employee with a Q in their name. The company will hire Angelique, Dominique, or Ezequiel. That will screw up the awesome Split Column solution from Fowmy.

Later, when Fowmy wants the 1, 2, 3, 4 to say Q1, Q2, Q3, Q4, use Format, Add Prefix:

Below is Fowmy’s code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter({"Q"}, QuoteStyle.Csv, false), {"Employee Name", "Qtr"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Employee Name"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Employee Name"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Category Description] <> "Dept. Total") and ([Column2] <> null)),
    #"Added Prefix" = Table.TransformColumns(#"Filtered Rows", {{"Column2", each "Q" & Text.From(_, "en-US"), type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Prefix", {"Category Description", "Column2"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Category Description", "Column2", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Column2]), "Column2", "Value", List.Sum),
    #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",{{"Category Description", "Employee Name"}, {"Attribute", "Category Description"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Employee Name", Order.Ascending}, {"Category Description", Order.Ascending}})
in
    #"Sorted Rows"

Jonathan Cooper sent in a variation on this technique. Jonathan used four steps to change “Q1” to “_Q1”, then “Q2” to “_Q2”, then “Q3” to “_Q3” then “Q4” to “_Q4”. Here is right after changing Q2:

Later, when Jonathan splits by the Underscore, he prevents Quentin from getting split. He also prevents later having to add the Q before the column.

I also like that Jonathan replaced all zeros with nulls in the final solution. Here is his code:

let
    Source = Excel.CurrentWorkbook(){[Name="UglyData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category Description", type text}, {"Dept. Total", type number}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", Int64.Type}, {"Employee 1", type number}, {"Q1_1", type number}, {"Q2_2", type number}, {"Q3_3", Int64.Type}, {"Q4_4", Int64.Type}, {"Employee 2", Int64.Type}, {"Q1_5", Int64.Type}, {"Q2_6", Int64.Type}, {"Q3_7", Int64.Type}, {"Q4_8", Int64.Type}, {"Employee 3", Int64.Type}, {"Q1_9", Int64.Type}, {"Q2_10", Int64.Type}, {"Q3_11", Int64.Type}, {"Q4_12", Int64.Type}, {"Employee 4", type number}, {"Q1_13", type number}, {"Q2_14", type number}, {"Q3_15", type number}, {"Q4_16", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category Description"}, "Attribute", "Value"),
    #"replace 0 with null" = Table.ReplaceValue(#"Unpivoted Other Columns",0,null,Replacer.ReplaceValue,{"Value"}),
    #"prep qtr to be split1" = Table.ReplaceValue(#"replace 0 with null","Q1","_Q1",Replacer.ReplaceText,{"Attribute"}),
    #"prep qtr to be split2" = Table.ReplaceValue(#"prep qtr to be split1","Q2","_Q2",Replacer.ReplaceText,{"Attribute"}),
    #"prep qtr to be split3" = Table.ReplaceValue(#"prep qtr to be split2","Q3","_Q3",Replacer.ReplaceText,{"Attribute"}),
    #"prep qtr to be split4" = Table.ReplaceValue(#"prep qtr to be split3","Q4","_Q4",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by underscore" = Table.SplitColumn(#"prep qtr to be split4", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Employee", "Period", "Attribute.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by underscore",{{"Employee", type text}, {"Period", type text}, {"Attribute.3", Int64.Type}}),
    #"prep to fill down" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"Employee"}),
    #"Filled Down employee" = Table.FillDown(#"prep to fill down",{"Employee"}),
    #"remove dept totals" = Table.SelectRows(#"Filled Down employee", each [Employee] <> "Dept. Total"),
    #"remove employee totals" = Table.SelectRows(#"remove dept totals", each [Period] <> null),
    #"Removed extra column" = Table.RemoveColumns(#"remove employee totals",{"Attribute.3"}),
    #"Pivoted Column" = Table.Pivot(#"Removed extra column", List.Distinct(#"Removed extra column"[Period]), "Period", "Value", List.Sum),
    #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum({[Q1], [Q2], [Q3], [Q4]}), type number),
    #"Sorted Rows" = Table.Sort(#"Inserted Sum",{{"Employee", Order.Ascending}, {"Category Description", Order.Ascending}})
in
    #"Sorted Rows"

Return to the main page for the Podcast 2316 challenge.

Read the next article in this series: Sorting the Line Items.