Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
Proud to be a Datanaut!
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:
Pete
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
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
Proud to be a Datanaut!