Forum Discussion
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 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 |
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 buildTableYou'll see that I've just changed the reference from the 'Source' step to your last removed columns step.
Pete
3 Replies
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 buildTableExample output:
Pete
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 buildTableYou'll see that I've just changed the reference from the 'Source' step to your last removed columns step.
Pete