Transform columns into rows

[Anonymous]

Hi everyone, all good?

I'm trying to transform columns into rows using Power Query for the following example table:

(actual table)

FatherID---	A	B	A	B
Y	10	20	11	13
W	80	50	5	7

I need to resume columns A,B (and more) into a single column called ID, for example: 

(Expected table)

FatherID--	ID--	Value1-	Value2-
Y	A	10	11
Y	B	20	13
W	A	80	5
W	B	50	7

I have tried to use the feature "Transform Columns into Rows", but I'm stuck with the following result:

FatherID--	ID1--	Value1-	ID2-	Value2-
Y	A	10	A	11
Y	A	10	B	13
Y	B	20	A	11
Y	B	20	B	13
W	A	80	A	5
W	A	80	B	7
W	B	50	A	5
W	B	50	B	7

The solution seems to be very simple but I can't help myself resolving it, could you guys help me with this?

[slorin]

Hi,

Another solution with Table.Group

let
Source = Your_Source,
ID = List.Range(Table.ColumnNames(Source), 1, (Table.ColumnCount(Source)-1)/2 ),
Col_Names = {"ID", "Value1", "Value2"},
Group = Table.Group(Source, {"FatherID"}, 
    {{"Data", each Table.FromColumns( {ID} & List.Split(List.Skip(Table.ToRows(_){0}), List.Count(ID)), Col_Names)}}),
Expand = Table.ExpandTableColumn(Group, "Data", Col_Names, Col_Names)
in
Expand

Stéphane 

[AlienSx]

Hi, @Anonymous a weird but names independent attempt

let
    // your_table with FartherID etc. as first row (not headers!) 
    Source = your_table,
    columns = Table.ToColumns(Source),
    data = List.Buffer(List.Skip(columns)),
    c_rows = Table.RowCount(Source) - 1,
    f_id = List.Buffer(List.Skip(columns{0})),
    v_id = List.Buffer(List.Distinct(List.Skip(Record.FieldValues(Source{0})))),
    collect = 
        List.Transform(
            v_id,
            (x) => {f_id} & {List.Repeat({x}, c_rows)} & List.Skip(List.Zip(List.Select(data, (w) => List.First(w) = x)))
        ),
    c_values = {1..List.Count(collect{0}) - 2},
    val_names = List.Transform(c_values, (x) => "Value" & Text.From(x)),
    tbl = Table.Combine(List.Transform(collect, (x) => Table.FromColumns(x, {"FatherID", "ID"} & val_names)))
in
    tbl

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this? Note, I could not name multiple columns with the same name though. 

Before:

After:

The trick here was to use unpivot columns 🙂 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilTSUTI0ABJGIMLQEEQYK8XqRCuFA5kWIEFTMAHE5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FatherID = _t, A = _t, B = _t, A.1 = _t, B.1 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"FatherID", "A.1", "B.1"}, "Attribute", "Value1"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(Source, {"FatherID", "A", "B"}, "Attribute", "Value2"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns1", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute", "Attribute.2"}),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"FatherID", "Attribute"}, #"Split Column by Delimiter", {"FatherID", "Attribute"}, "Split Column by Delimiter", JoinKind.LeftOuter),
    #"Expanded Split Column by Delimiter" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Delimiter", {"Value2"}, {"Value2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Split Column by Delimiter",{"A.1", "B.1"})
in
    #"Removed Columns"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ 

[Anonymous]

Hey! I got it here, the trick I see was using unpivot columns separately, them merging both new columns, that was a very good trick hahaha!