Forum Discussion
Transform columns into rows
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?
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/
4 Replies
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/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 tblHi,
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
ExpandStéphane
