Forum Discussion
Unpivoting clustered columns
Like, currently I have it like this:
| Id | V1 | V2 | V3 | A1 | A2 | A3 | B1 | B2 | B3 |
| 1 | v1.1 | v2.1 | v3.1 | a1.1 | a2.1 | a3.1 | b1 .1 | b2 .1 | b3.1 |
| 2 | v1.2 | v2.2 | v3.2 | a1.2 | a2.2 | a3.2 | b1 .2 | b2 .2 | b3.2 |
| 3 | v1.3 | v2.3 | v3.3 | a1.3 | a2.3 | a3.3 | b1 .3 | b2 .3 | b3.3 |
But I need it like this:
| Id | Category | V | A | B |
| 1 | 1 | v1.1 | a1.1 | b1 .1 |
| 1 | 2 | v2.1 | a2.1 | b2.1 |
| 1 | 3 | v3.1 | a3.1 | b3.1 |
| 2 | 1 | v2.1 | a2.2 | b2.1 |
| 2 | 2 | v2.2 | a2.2 | b2.2 |
| 2 | 3 | v2.3 | a3.2 | b2.3 |
How do I do this? Like, I can unpivot one column, like the V-columns. But then it becomes a pain if I unpivot the A and B column separately to then merge them, but it feel unnecessary time consuming. Besides that, I would have a long list of queries in my Power Query to manage with dozens of mergens because they made a mess of the data. Like, above with V-A-B, I have several more of such sets in the table. Basically they created a god table that I want to split up.
Is there a more effective manner than have 3 queries per 3 columns and follow it with a merge?
10 Replies
=Table.FromPartitions("Id",Table.ToList(YourTable,each {_{0},Table.FromColumns(List.Split(List.Skip(_),3),{"V","A","B"})}))
I presume this one has the same trouble as the other, that sometimes my field names are not just but V1 V2, but sometimes are V1: V2, this the trailing characters are not always numbers.
Merge the columns into V A and B. Then split the collumns checking the box to split into rows.
Another possible method using your example table...
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText("LcyxCcBADATBVozix6C7csQH7yJcv7FW0cIGUxUZK968OyLuHOZhHuaTF9X033tVCEYwghGMYASjYTSMYNSMYQxjGMMYxjAexsMYxrH3Bw==", BinaryEncoding.Base64), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, V1 = _t, V2 = _t, V3 = _t, A1 = _t, A2 = _t, A3 = _t, B1 = _t, B2 = _t, B3 = _t] ), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( Source, {"Id"}, "Category", "Value" ), #"Split Column by Character Transition" = Table.SplitColumn( #"Unpivoted Other Columns", "Category", Splitter.SplitTextByCharacterTransition( (c) => not List.Contains({"0".."9"}, c), {"0".."9"} ), {"Category.2", "Category"} ), #"Grouped Rows" = Table.Group( #"Split Column by Character Transition", {"Id", "Category"}, {{"_grouped", each _, type table [Id=nullable text, Attribute.1=nullable text, Attribute.2=nullable text, Value=text]}} ), Custom1 = Table.TransformColumns( #"Grouped Rows", { "_grouped", each Table.Pivot( Table.SelectColumns( _, {"Category.2", "Value"} ), List.Distinct( Table.SelectColumns( _, {"Category.2", "Value"} )[Category.2] ), "Category.2", "Value" ) } ), #"Expanded _grouped" = Table.ExpandTableColumn( Custom1, "_grouped", {"V", "A", "B"}, {"V", "A", "B"} ) in #"Expanded _grouped"I'm seeing this part there:
Would it break that part, or something else, if the V columns sometimes have a trailing character like ":"?
Like this:
Like, the source is data from a SharePoint form and they messed up the back big time. They just expect me to do magic or something.
Right now it is splitting the column where it transitions from letter to number.
If there are trailing characters after the number it will 'break' the grouping step that follows.
It sounds like you may need to add a step (or two) after the split to clean the category column to allow for the grouping to occur correctly.
Unpivot
Split Attribute from Non Digit to Digit to end up with Attribute.1 and Attribute.2
Split the Attribute.2 from Digit to non digit to get rid of the extra ":" if it exists.
Do any additional clean up on Attribute.1 and attribute 2.1 that you need to properly pivot your data into the correct columns.
Then pivot.
