Combine Multiple Columns of Data into a Single Column Spread Out Across Rows

[pbidevcr]

Hi all, 

I have an excel file with multiple columns (years) and values by country. The format is like this:

Country Name	Country Code	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010
Aruba	ABW	4.044021312	2.883604303	3.315774991	3.657376581	2.529938059	3.395625246	3.608711459	5.39120264	8.957732188	-2.136371573	2.07773902
Afganistán	AFG						12.68626872	7.254895561	8.482889268	30.55494061	-8.283078395	0.892536936
Angola	AGO	324.9968716	152.5610225	108.8974361	98.2237177	43.54194725	22.96374444	13.30325336	12.24867552	12.47371341	13.73145114	14.4705412
Albania	ALB	0.050018136	3.10758827	7.770525834	0.484002612	2.280019169	2.366581957	2.370728319	2.932682482	3.359242418	2.280502222	3.552267388

My question is:  how can I get all of those columns into a single “Year” column so that I can analyze the data more efficiently in Power BI? I need to spread out the values across rows.

Appreciate your help!

Best!

[ChandeepChhabra · Accepted solution]

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

[ChandeepChhabra · Accepted solution]

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

[pbidevcr]

Hi @ChandeepChhabra

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 · Accepted solution]

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

[pbidevcr]

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!