Append Dynamically

[Anonymous]

My requirement should be fairly achievavble (to all the PQ Experts 🙂 

I have data coming in horizontally and I want to stack certain columns below it. The below SS will tell you better. Please advise..

This is what I have 

This is what I need

**Note: The table will continue to expand horizontally. So the stacking must happen dynamically**

[AlexisOlson · Accepted solution]

1. Split the list of column names into pairs using List.Split.
2. Combine all the number columns by taking the values from the first column name in each pair.
3. Combine all the text columns by taking the values from the second column name in each pair.
4. Combine these two lists into a single table using Table.FromColumns.

Here's a full sample query you can paste into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYhMgTgZicyBOU4rViVYyArKSgNgUiFOA2AKI08EyxlC1ZkCcCsSWQJyhFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}}),
    ColPairs = List.Split(Table.ColumnNames(#"Changed Type"), 2),
    NumberCol = List.Combine(List.Transform(List.Transform(ColPairs, each _{0}), each Table.Column(#"Changed Type", _))),
    TextCol = List.Combine(List.Transform(List.Transform(ColPairs, each _{1}), each Table.Column(#"Changed Type", _))),
    Combined = Table.FromColumns({NumberCol, TextCol}, {"Number", "Text"})
in
    Combined

[Anonymous]

Thanks a lot!

[AlexisOlson]

When you say "continue to expand horizontally", do you mean you'll be adding additional columns (and they'll always come in ordered pairs of number and text)?

[Anonymous]

Yes thats right. It will always be ordered and same data type.