Unpivoting clustered columns

[DouweMeer]

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? 

[spinfuzer]

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.

[wdx223_Daniel]

=Table.FromPartitions("Id",Table.ToList(YourTable,each {_{0},Table.FromColumns(List.Split(List.Skip(_),3),{"V","A","B"})}))

[DouweMeer]

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.

[jgeddes]

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"

[DouweMeer]

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. 

[Anonymous]

Hi @DouweMeer 

You can put the  following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcyxCcAwEATBVszHwqC7ch4FchGu31h70cIG012zRr3zPhHxyWZu5mY+86JK/XeNLuEIRzjCEY5wFEdxFEfHMY5xjGMc4xjHcRzHcVxrfQ==", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"V1", type text}, {"V2:", type text}, {"V3", type text}, {"A1:", type text}, {"A2,", type text}, {"A3", type text}, {"B1 ", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine(
  List.RemoveNulls(
    List.Transform(
      Text.ToList([Attribute.2]),
    each if Value.Is(Value.FromText(_), type number) 
    then _ else null)
    )
  )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.1]), "Attribute.1", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jgeddes]

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.

[DouweMeer]

The nasty thing with this is that it is a simplification as well. There are 40 columns per category and no guarantee that the field name remain the same :). I forsee a lot of trouble during the scheduled refreshes...

Like, as well, I don't understand how your code works, nor the one from community support. How am I supposed to trouble  shoot something I don't understand? Like, I have sufficient experience with Power BI to have a rough understanding what it does so I can cherry pick of a few uncertainties, but that's it. 

I think the concatenation solution from j_ocean might be the best as it is the simplest and thereby guarantees for me continuity. Like, I'll wait a bit for your response. Perhaps your code is really easy to understand and trouble shoot, but otherwise I'll mark the merge of the column and after unpivot splitting them again as the solution. 

[jgeddes]

The basic premise of the solution I provided was that it takes the existing columns that need to be split and turns them into rows with their coresponding values. (The unpivot step.) From there the critical piece of the solution is having a consistent method of splitting the 'Category' column into the two columns you desire. This step can be as simple as the example where you split by transition from letter to number or it can be a complex, multi step process that uses conditional logic to split the column.
Once the 'Category' column has been split my solution groups the rows based on the 'Id' and resulting 'Category' column. (Grouped Rows step)

The 'Custom1' step works on the inner tables that were created in the 'Grouped Rows' step. In this step I select the second split category ('Category.2') and the 'Value' columns from each of the inner tables and then pivots those two columns so that they can be expanded back into the main table.

The 'Expanded _grouped' step does just that.

Again, the critical piece of this solution is having logic that will split your column headers into the two columns you desire. The rest of the solution is really just table manipulation. As an aside, the solution from @wdx223_Daniel  does all of this in one line. It is a brillant piece of code.
Hope this helps a bit.

[j_ocean]

Merge the columns into V A and B. Then split the collumns checking the box to split into rows.