Forum Discussion
Combine Multiple Columns of Data into a Single Column Spread Out Across Rows
Hi pbidevcr
Use the following power query code. Note that I have converted your data into a table (named country)
let Source = Excel.CurrentWorkbook(){[Name="Country"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Country", "CODE"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}}) in #"Renamed Columns"The result looks like this
You can download the solution Excel file here
thanks
Hi pbidevcr
The code will remain the same for Power BI as well. The only change being that your source file will be an excel workbook
That way you can keep the source file and output (PowerBi) separate
Hope it helps
Impactful IndividualHi pbidevcr
Use the following power query code. Note that I have converted your data into a table (named country)
let
Source = Excel.CurrentWorkbook(){[Name="Country"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Country", "CODE"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}})
in
#"Renamed Columns"
The result looks like this
You can download the solution Excel file here
thanks
I tested this code in my own excel file and it worked without any issues. Is there a way to run this in Power BI? Do you recommend to make this in the source file instead?
Thank you!
- ChandeepChhabra
Hi pbidevcr
The code will remain the same for Power BI as well. The only change being that your source file will be an excel workbook
That way you can keep the source file and output (PowerBi) separate
Hope it helps
Hi ChandeepChhabra,
There is another way to solve this challenge within Power BI. See the following video:
Unpivot Column in Power BI
https://www.youtube.com/watch?v=W6UeZCte9YM
Thanks a lot for your support!