Forum Discussion
Stacking multiple Table columns in Power Query
- Anonymous3 years ago
I don't know if this is the fastest way, but i would do it like this:
- Unpivot volume columns
- Unpivot size columns.
- Split both attribute columns by number of characters, so you will have the relevant number.
- Add a custom column that checks if the numbers for size and volume are the same, filter this to true.
- Remove irrelevant columns.
This is the query code:
let
Source = Excel.Workbook(File.Contents("C:\....Map2.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Blad1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Record", type text}, {"Size1", Int64.Type}, {"Volume1", type number}, {"Size2", Int64.Type}, {"Volume2", type number}, {"Size3", Int64.Type}, {"Volume3", type number}, {"Size4", Int64.Type}, {"Volume4", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record", "Size1", "Size2", "Size3", "Size4"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Record", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns1", "Attribute", Splitter.SplitTextByRepeatedLengths(6), {"Attribute.1.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Position",{{"Attribute.2", "Volume number"}}),
#"Split Column by Position1" = Table.SplitColumn(#"Renamed Columns", "Attribute.1", Splitter.SplitTextByRepeatedLengths(4), {"Attribute.1.1.1", "Attribute.1.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Split Column by Position1",{{"Attribute.1.2", "Size Number"}, {"Value", "Volume"}, {"Value.1", "Size"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each [Volume number]=[Size Number]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Record", "Size", "Volume"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Size", Order.Ascending}})
in
#"Sorted Rows"
I don't know if this is the fastest way, but i would do it like this:
- Unpivot volume columns
- Unpivot size columns.
- Split both attribute columns by number of characters, so you will have the relevant number.
- Add a custom column that checks if the numbers for size and volume are the same, filter this to true.
- Remove irrelevant columns.
This is the query code:
let
Source = Excel.Workbook(File.Contents("C:\....Map2.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Blad1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Record", type text}, {"Size1", Int64.Type}, {"Volume1", type number}, {"Size2", Int64.Type}, {"Volume2", type number}, {"Size3", Int64.Type}, {"Volume3", type number}, {"Size4", Int64.Type}, {"Volume4", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record", "Size1", "Size2", "Size3", "Size4"}, "Attribute", "Value"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Record", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns1", "Attribute", Splitter.SplitTextByRepeatedLengths(6), {"Attribute.1.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Position",{{"Attribute.2", "Volume number"}}),
#"Split Column by Position1" = Table.SplitColumn(#"Renamed Columns", "Attribute.1", Splitter.SplitTextByRepeatedLengths(4), {"Attribute.1.1.1", "Attribute.1.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Split Column by Position1",{{"Attribute.1.2", "Size Number"}, {"Value", "Volume"}, {"Value.1", "Size"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each [Volume number]=[Size Number]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Record", "Size", "Volume"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Size", Order.Ascending}})
in
#"Sorted Rows"
Thanks!