Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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 @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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |