Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
piyushjh
Frequent Visitor

Appending columns Power BI

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 1Column 2Column 3Column 4Column 5
Data 1Value 1Value 2Value 3Value 4
Data 2Value 1Value 2Value 3Value 4
Data 3Value 1Value 2Value 3Value 4
Data 4Value 1Value 2Value 3Value 4

 

I want the Data in below format

 

Column 1Column 2Column 3
Data 1Value 1Value 2
Data 2Value 1Value 2
Data 3Value 1Value 2
Data 4Value 1Value 2
Data 1Value 3Value 4
Data 2Value 3Value 4
Data 3Value 3Value 4
Data 4Value 3Value 4
1 ACCEPTED SOLUTION

Hi @piyushjh ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

Hi @piyushjh ,

 

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:

BA_Pete_1-1669631756807.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

piyushjh_0-1669701138389.png

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

 

 

Hi @piyushjh ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors