Appending columns Power BI

[Anonymous]

hi,

I want to append /stack columns in POwer BI. I have the data in the below format and want to stakc them one after another.

Is there a way to do it . 

Column 1	Column 2	Column 3	Column 4	Column 5
Data 1	Value 1	Value 2	Value 3	Value 4
Data 2	Value 1	Value 2	Value 3	Value 4
Data 3	Value 1	Value 2	Value 3	Value 4
Data 4	Value 1	Value 2	Value 3	Value 4

I want the Data in below format

Column 1	Column 2	Column 3
Data 1	Value 1	Value 2
Data 2	Value 1	Value 2
Data 3	Value 1	Value 2
Data 4	Value 1	Value 2
Data 1	Value 3	Value 4
Data 2	Value 3	Value 4
Data 3	Value 3	Value 4
Data 4	Value 3	Value 4

[BA_Pete]

Hi @Anonymous ,

You can do this quickly by breaking the source up into lists then recombining them:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVQwVNJRCkvMKU1FYhnBWcZwlolSrA5UixHpWoxJ12JCvJZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t]),
    getList1 = Source[Column 1] & Source[Column 1],
    getList2 = Source[Column 2] & Source[Column 4],
    getList3 = Source[Column 3] & Source[Column 5],
    
    buildTable = Table.FromColumns({getList1, getList2, getList3},{"Column1", "Column2", "Column3"})
in
    buildTable

Example output:

Pete

[Anonymous]

Hi,

I have the source as the excel workbook and have done some transformations also , need to add it as a source how can I do it

#RemovedColumns is the final version

Source=Excel.Workbook(File.Contents("Test.xlsx"), null, true)

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Open up Advanced Editor for your query and paste this onto the bottom, overwriting the current 'in #"Removed Columns1" section at the end:

    getList1 = #"Removed Columns1"[Column 1] & #"Removed Columns1"[Column 1],
    getList2 = #"Removed Columns1"[Column 2] & #"Removed Columns1"[Column 4],
    getList3 = #"Removed Columns1"[Column 3] & #"Removed Columns1"[Column 5],
    
    buildTable = Table.FromColumns({getList1, getList2, getList3},{"Column1", "Column2", "Column3"})
in
    buildTable

You'll see that I've just changed the reference from the 'Source' step to your last removed columns step.

Pete