Stacking multiple Table columns in Power Query

[Salle]

Hi everyone,

I am trying to figure out how to stack mulitple columns in a  smart way. The below tables shows an example of the data from Doc1 (Doc1, Doc2, Doc3...)

Record	Size1	Volume1	Size2	Volume2	Size3	Volume3	Size4	Volume4
Doc1	1	0,1	4	0,4	7	0,7	10	0,10
Doc1	2	0,2	5	0,5	8	0,8
Doc1	3	0,3	6	0,6	9	0,9
Desired result:
Record	Size	Volume
Doc1	1	0,1
Doc1	2	0,2
Doc1	3	0,3
Doc1	4	0,4
Doc1	5	0,5
Doc1	6	0,6
Doc1	7	0,7
Doc1	8	0,8
Doc1	9	0,9
Doc1	10	0,10

I have tried with unpivot and it works when I only unpivot Size or Volume.
But how can I achive the desired result and keep the order of size vs volume values?

Thanks in advance

[slorin]

Another solution with Table.Group

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslPNlTSUQJhAx0QaQJmgUhzMAtEGhpApWN14DqMwEIg0hTMApEWYBaIBCEkxcZgCRBpBmaBSEswyxKmOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t, Size1 = _t, Volume1 = _t, Size2 = _t, Volume2 = _t, Size3 = _t, Volume3 = _t, Size4 = _t, Volume4 = _t]),
Group = Table.Group(Source, {"Record"},
{{"Data", each Table.FromColumns({
List.Combine(List.Alternate(Table.ToColumns(_),1,1)),
List.Combine(List.Skip(List.Alternate(Table.ToColumns(_),1,1,1)))},
{"Size","Volume"})}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Size", "Volume"}, {"Size", "Volume"})
in
Expand

Stéphane 

[Salle]

Thanks @slorin. 
This solution looks nice, I am a bit new to using the grouping function but will defently try this out.

[Anonymous · Accepted solution]

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"

[Salle]

Thanks!