Forum Discussion
Solved
Turn repeating row values into column headers?
I have a dataset that tracks the progress of various milestones across multiple projects. The projects are in rows and multiple attributes about each milestone are tracked in columns. I track both th...
After doing your unpivot, you can
- Split out the Number for the Milestone column
- Add an extra column that is shifted by one from the Value column
- Remove every other row from the table, starting with row 2
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Project", type text}, {"Milestone 1 - Name", type text}, {"Milestone 1 - Percent Complete", type number}, {"Milestone 2 - Name", type text}, {"Milestone 2 - Percent Complete", type number}, {"Milestone 3 - Name", type text}, {"Milestone 3 - Percent Complete", type number}, {"Milestone 4 - Name", type text}, {"Milestone 4 - Percent Complete", type number}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"), #"Add Milestone" = Table.AddColumn(#"Unpivoted Other Columns", "Milestone", each Number.From(Text.Split([Attribute]," "){1}), type number), #"Reordered Columns" = Table.ReorderColumns(#"Add Milestone",{"Project", "Milestone", "Attribute", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}), //Offset Percentage column #"Add Offset Col" = Table.FromColumns( Table.ToColumns(#"Removed Columns") & {List.RemoveFirstN(#"Removed Columns"[Value]) & {null}}, type table[Project=text, Milestone=Int64.Type, Name=text, Percent Complete = Percentage.Type]), #"Removed Alternate Rows" = Table.AlternateRows(#"Add Offset Col",1,1,1) in #"Removed Alternate Rows"
After doing your unpivot, you can
- Split out the Number for the Milestone column
- Add an extra column that is shifted by one from the Value column
- Remove every other row from the table, starting with row 2
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Project", type text}, {"Milestone 1 - Name", type text}, {"Milestone 1 - Percent Complete", type number}, {"Milestone 2 - Name", type text}, {"Milestone 2 - Percent Complete", type number}, {"Milestone 3 - Name", type text}, {"Milestone 3 - Percent Complete", type number}, {"Milestone 4 - Name", type text}, {"Milestone 4 - Percent Complete", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
#"Add Milestone" = Table.AddColumn(#"Unpivoted Other Columns", "Milestone",
each Number.From(Text.Split([Attribute]," "){1}), type number),
#"Reordered Columns" = Table.ReorderColumns(#"Add Milestone",{"Project", "Milestone", "Attribute", "Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
//Offset Percentage column
#"Add Offset Col" = Table.FromColumns(
Table.ToColumns(#"Removed Columns") &
{List.RemoveFirstN(#"Removed Columns"[Value]) & {null}},
type table[Project=text, Milestone=Int64.Type, Name=text, Percent Complete = Percentage.Type]),
#"Removed Alternate Rows" = Table.AlternateRows(#"Add Offset Col",1,1,1)
in
#"Removed Alternate Rows"
AH-MAZING! Thank you so much!
I had to add a step to replace null percent complete values with 0s so my unpivot would result in perfect alternating rows, but it works! I love the outside-the-box thinking. I will review this more closely and see if I can replicate the outcome in my other report where I do the same thing. Thanks again!