Forum Discussion
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 | Category1 | Action2 | Category2 | Action3 | Category3 |
| ABC | West | John Smith | Sign Document | Office | Empty Bin | Waste | ||
| XYZ | North | John Doe | Make Copies | Office | ||||
| 123 | South | Jane Doe | Send Email | Office | Recycle boxes | Waste |
Not all action columns will have text in it; if they do, then the category column will always be filled in next to it. (IE. a category column will be empty unless there is text in the action category to the left of it).
I would like to extract each action/category pairing into its own row if there is any text in the action categories, so it is formatted like this:
| Account | Region | Manager | Action | Category |
| ABC | West | John Smith | Sign Document | Office |
| ABC | West | John Smith | Empty Bin | Waste |
| XYZ | North | John Doe | Make Copies | Office |
| 123 | South | Jane Doe | Send Email | Office |
| 123 | South | Jane Doe | Recycle Boxes | Office |
I've tried combinations of selecting unpivot columns but nothing is working for me. Any ideas?
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"
2 Replies
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"AlexisOlson Thank you! It worked.
