Forum Discussion
Niels_83
Nov 15, 2025Copper Contributor
unpivot from columns to rows in power query
I want to transpose data from different columns to rows:
from:
to:
any suggestions how to do so in power query?
2 Replies
- SergeiBaklanDiamond Contributor
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], First = Table.SelectColumns(Source,{"Naam", "employee1", "share employee1"}), Second = Table.SelectColumns(Source,{"Naam", "employee2", "share employee2"}), names = {"Naam", "employee", "share employee"}, append = Table.RenameColumns( First, List.Zip( { Table.ColumnNames(First), names } ) ) & Table.RenameColumns( Second, List.Zip( { Table.ColumnNames(Second), names } ) ), SortIt = Table.Sort(append,{{"Naam", Order.Ascending}}) in SortItif I understood the logic correctly
- OliverScheurichGold Contributor
let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Naam"}, "Attribut", "Wert"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter), #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Wert"}, {"Wert.1"}), #"Inserted Modulo" = Table.AddColumn(#"Expanded {0}", "Modulo", each Number.Mod([Index], 2), type number), #"Filtered Rows" = Table.SelectRows(#"Inserted Modulo", each ([Modulo] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribut", "Index", "Index.1", "Modulo"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Wert", "employee"}, {"Wert.1", "share employee"}}) in #"Renamed Columns"The M code above returns the output in the green result table with basic transformations. The name of the blue table is "Tabelle1".