Forum Discussion
Solved
Unpivot 2 Paired Columns together
Hello-- I would like to unpivot each paired Action/Category columns if there is any text in any of the action categories. This is the format of my data: Account Region Manager Action1 Cate...
Unpivot all columns except the first three columns,
Filter blank values and split off the number suffix:
Pivot the attribute column:
Full sample query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7NCsIwDADgVwk976I+gfu5CCrYgz9lh1ozV7Y2w3bg3t627jD0EJIQ8iVCsG1esIyd0fmQdtRa4Eb7NjRcPy2UpEaDNg6PTaMVhgLmqMzgJ8i1jYB0HlmdCXa53kJ/oFdCkljScm0vO4SCBo3uX43Car2J52n8CtLij8DRPqAyUvdL4IRqUj3Cnd5Jnl+qPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Region = _t, Manager = _t, Action1 = _t, Category1 = _t, Action2 = _t, Category2 = _t, Action3 = _t, Category3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account", "Region", "Manager"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")), #"Split Column by Positions" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByPositions({0,1}, true), {"Attribute", "Index"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Positions", List.Distinct(#"Split Column by Positions"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
Unpivot all columns except the first three columns,
Filter blank values and split off the number suffix:
Pivot the attribute column:
Full sample query you can paste into the Advanced Editor of a new blank query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7NCsIwDADgVwk976I+gfu5CCrYgz9lh1ozV7Y2w3bg3t627jD0EJIQ8iVCsG1esIyd0fmQdtRa4Eb7NjRcPy2UpEaDNg6PTaMVhgLmqMzgJ8i1jYB0HlmdCXa53kJ/oFdCkljScm0vO4SCBo3uX43Car2J52n8CtLij8DRPqAyUvdL4IRqUj3Cnd5Jnl+qPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Region = _t, Manager = _t, Action1 = _t, Category1 = _t, Action2 = _t, Category2 = _t, Action3 = _t, Category3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Account", "Region", "Manager"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")),
#"Split Column by Positions" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByPositions({0,1}, true), {"Attribute", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Positions", List.Distinct(#"Split Column by Positions"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"