Forum Discussion
Solved
Unpivot multiple columns that may have nulls
I've been trying to figure out way to unpivot data where an issue / priority and status is split on a delimiter. Each item may have 0..n issues attached to it. Thats been represented in a single colu...
Hi,
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Types" = Table.TransformColumnTypes(Source,{{"Item Name", type text}, {"Issue(s)", type text}}), #"Removed Rows with No Issues" = Table.SelectRows(#"Changed Types", each ([#"Issue(s)"] <> null)), #"Split by Semicolon" = Table.SplitColumn(#"Removed Rows with No Issues", "Issue(s)", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"Issue(s).1", "Issue(s).2", "Issue(s).3"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split by Semicolon", {"Item Name"}, "Attribute", "Value"), #"Removed Attribute Column" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Split by /" = Table.SplitColumn(#"Removed Attribute Column", "Value", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Issue A", "Issue B", "Issue C"}) in #"Split by /"Obviously amend the first two lines as required.
Regards
Jos_Woolley
Solution Sage
Solution SageHi,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Types" = Table.TransformColumnTypes(Source,{{"Item Name", type text}, {"Issue(s)", type text}}),
#"Removed Rows with No Issues" = Table.SelectRows(#"Changed Types", each ([#"Issue(s)"] <> null)),
#"Split by Semicolon" = Table.SplitColumn(#"Removed Rows with No Issues", "Issue(s)", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"Issue(s).1", "Issue(s).2", "Issue(s).3"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split by Semicolon", {"Item Name"}, "Attribute", "Value"),
#"Removed Attribute Column" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Split by /" = Table.SplitColumn(#"Removed Attribute Column", "Value", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), {"Issue A", "Issue B", "Issue C"})
in
#"Split by /"Obviously amend the first two lines as required.
Regards
Thanks Jos - whilst I couldn't get the MCode to work I was able to step through what you'd done in the PQ Editor GUI and bang on thats what i wanted to achieve.
- Jos_Woolley
You're welcome!
Cheers