Hey Lisaeu,
This looks like a fun table to clean up. I’m sure there are multiple ways to achieve your end result, here’s my solution:
- Select the date column and unpivot the other columns
- Add a new column by extracting the first word of the “Attribute” column, this will be your cancel type. In my example I called this “Type”.
- Add another new column by extracting the last word of the “Attribute” column to define count vs. value rows. In my example I called this “Cat”.
- Remove the Attribute column (it’s irrelevant after adding the new columns)
- Pivot the “Cat” column and select the Values column.
I hope this works for you.
Here’s the M Code:
let
Source = Excel.Workbook(File.Contents("G:\My Drive\Learning\Enterprise DNA Master Class\Forum Questions\Power Query - Unpivot.xlsx"), null, true),
#"Current Data Table_Sheet" = Source{[Item="Current Data Table",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Current Data Table_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Blue Cancel Count", Int64.Type}, {"Blue Cancel Value", Int64.Type}, {"Red Cancel Count", Int64.Type}, {"Red Cancel Value", Int64.Type}, {"Yellow Cancel Count", Int64.Type}, {"Yellow Cancel Value", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each Text.BeforeDelimiter([Attribute], " "), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Cat", each Text.AfterDelimiter([Attribute], " ", {0, RelativePosition.FromEnd}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Cat]), "Cat", "Value", List.Sum)
in
#"Pivoted Column"
