Forum Discussion
Need to Transpose Countries over Years, For Each Country List All Year Values
Hi. Basically I have data that looks like this:
| 1996 | 1998 | 2000 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | |
| ABW | #N/A | #N/A | #N/A | #N/A | #N/A | 0.74 | 1.15 | 1.02 | 0.97 | 0.97 | 1.25 | 1.28 | 1.27 | 1.28 | 1.28 | 1.28 | 1.27 | 1.28 | 1.30 | 1.27 | 1.26 | 1.29 | 1.03 |
| ADO | 1.56 | 1.53 | 1.54 | 1.44 | 1.42 | 1.56 | 1.53 | 1.33 | 1.31 | 1.33 | 1.32 | 1.32 | 1.36 | 1.43 | 1.41 | 1.17 | 1.19 | 1.18 | 1.16 | 1.04 | 1.11 | 1.09 | 1.04 |
| AFG | -1.91 | -2.04 | -2.03 | -1.43 | -1.18 | -1.20 | -1.13 | -1.11 | -1.06 | -1.17 | -1.38 | -1.40 | -1.34 | -1.27 | -1.24 | -1.14 | -1.12 | -1.04 | -0.99 | -1.01 | -1.01 | -1.08 | -1.57 |
| AGO | -1.58 | -1.41 | -1.46 | -1.24 | -1.27 | -1.29 | -1.23 | -1.25 | -1.19 | -1.12 | -1.13 | -1.12 | -1.13 | -1.08 | -1.11 | -1.15 | -1.18 | -1.14 | -1.10 | -0.91 | -0.78 | -0.80 | -0.84 |
| ALB | -0.65 | -0.39 | -0.29 | -0.01 | 0.07 | 0.01 | 0.00 | 0.08 | 0.11 | 0.17 | 0.14 | 0.12 | 0.06 | 0.02 | 0.05 | 0.14 | 0.16 | 0.17 | 0.20 | 0.18 | 0.14 | 0.09 | 0.09 |
| ARE | -0.41 | -0.52 | -0.52 | -0.63 | -0.91 | -0.69 | -0.71 | -1.00 | -0.91 | -0.91 | -0.84 | -0.90 | -0.90 | -1.00 | -1.02 | -1.06 | -1.11 | -1.05 | -1.10 | -1.13 | -1.14 | -1.18 | -1.19 |
| ARG | 0.39 | 0.31 | 0.42 | 0.26 | 0.35 | 0.36 | 0.27 | 0.40 | 0.45 | 0.36 | 0.28 | 0.36 | 0.34 | 0.30 | 0.28 | 0.35 | 0.41 | 0.49 | 0.53 | 0.53 | 0.57 | 0.59 | 0.62 |
| ARM | -0.57 | -0.34 | -0.37 | -0.44 | -0.47 | -0.57 | -0.59 | -0.74 | -0.76 | -0.86 | -0.88 | -0.85 | -0.69 | -0.57 | -0.59 | -0.56 | -0.54 | -0.58 | -0.56 | -0.14 | 0.06 | 0.05 | 0.06 |
| ASM | #N/A | #N/A | #N/A | #N/A | #N/A | 0.50 | 0.65 | 0.55 | 0.97 | 0.97 | 0.96 | 0.99 | 1.01 | 1.03 | 1.00 | #N/A | #N/A | #N/A | #N/A | 1.46 | 1.27 | 1.06 | 0.91 |
| ATG | 0.33 | 0.34 | 0.39 | 0.34 | 0.32 | 0.57 | 0.53 | 0.63 | 0.54 | 0.57 | 0.59 | 0.50 | 0.51 | 0.64 | 0.62 | 0.56 | 0.60 | 0.63 | 0.61 | 0.57 | 0.73 | 0.75 | 0.74 |
| AUS | 1.44 | 1.41 | 1.47 | 1.41 | 1.44 | 1.50 | 1.51 | 1.38 | 1.37 | 1.37 | 1.38 | 1.42 | 1.45 | 1.50 | 1.44 | 1.36 | 1.36 | 1.35 | 1.38 | 1.38 | 1.27 | 1.30 | 1.38 |
| AUT | 1.45 | 1.35 | 1.32 | 1.31 | 1.34 | 1.48 | 1.38 | 1.37 | 1.37 | 1.36 | 1.39 | 1.43 | 1.40 | 1.45 | 1.46 | 1.39 | 1.38 | 1.34 | 1.39 | 1.39 | 1.34 | 1.40 | 1.40 |
| AZE | -1.12 | -1.02 | -0.91 | -0.98 | -1.04 | -1.06 | -1.12 | -1.28 | -1.23 | -1.33 | -1.26 | -1.30 | -1.30 | -1.30 | -1.40 | -1.48 | -1.55 | -1.56 | -1.56 | -1.51 | -1.53 | -1.54 | -1.53 |
| BDI | -1.55 | -1.44 | -1.49 | -1.16 | -1.18 | -1.28 | -0.63 | -0.72 | -0.71 | -0.69 | -0.69 | -0.94 | -0.93 | -0.89 | -0.95 | -1.04 | -1.27 | -1.56 | -1.58 | -1.67 | -1.72 | -1.53 | -1.41 |
| BEL | 1.43 | 1.34 | 1.37 | 1.38 | 1.48 | 1.44 | 1.42 | 1.34 | 1.36 | 1.32 | 1.35 | 1.36 | 1.33 | 1.35 | 1.37 | 1.37 | 1.39 | 1.38 | 1.34 | 1.32 | 1.31 | 1.28 | 1.28 |
| BEN | 0.26 | 0.43 | 0.41 | 0.05 | 0.19 | 0.05 | -0.03 | 0.36 | 0.37 | 0.34 | 0.34 | 0.31 | 0.17 | 0.12 | 0.16 | 0.26 | 0.29 | 0.41 | 0.38 | 0.24 | 0.06 | -0.07 | -0.24 |
| BFA | -0.53 | -0.40 | -0.23 | -0.39 | -0.31 | -0.43 | -0.45 | -0.34 | -0.31 | -0.28 | -0.29 | -0.25 | -0.28 | -0.30 | -0.26 | -0.35 | -0.11 | 0.01 | 0.06 | -0.07 | -0.20 | -0.18 | -0.11 |
| BGD | -0.06 | -0.09 | -0.23 | -0.43 | -0.55 | -0.67 | -0.57 | -0.45 | -0.57 | -0.44 | -0.29 | -0.27 | -0.32 | -0.40 | -0.41 | -0.47 | -0.51 | -0.58 | -0.62 | -0.75 | -0.74 | -0.77 | -0.77 |
| BGR | 0.43 | 0.44 | 0.46 | 0.53 | 0.55 | 0.57 | 0.59 | 0.58 | 0.69 | 0.58 | 0.56 | 0.53 | 0.45 | 0.40 | 0.34 | 0.37 | 0.43 | 0.40 | 0.43 | 0.36 | 0.36 | 0.26 | 0.29 |
| BHR | -0.72 | -1.07 | -1.06 | -0.57 | -0.59 | -0.56 | -0.74 | -0.90 | -0.86 | -0.87 | -0.79 | -0.97 | -1.22 | -1.32 | -1.32 | -1.31 | -1.31 | -1.37 | -1.39 | -1.42 | -1.43 | -1.45 | -1.50 |
| BHS | 1.10 | 1.16 | 1.20 | 1.19 | 1.03 | 0.98 | 0.94 | 1.02 | 1.10 | 1.12 | 1.01 | 0.99 | 0.96 | 0.93 | 0.92 | 1.05 | 0.95 | 0.85 | 0.85 | 0.76 | 0.91 | 0.92 | 0.91 |
| BIH | -0.10 | -0.01 | -0.11 | -0.09 | 0.21 | 0.17 | 0.21 | 0.19 | 0.13 | 0.02 | 0.00 | -0.08 | -0.16 | -0.10 | -0.12 | -0.07 | -0.10 | -0.13 | -0.21 | -0.27 | -0.24 | -0.32 | -0.31 |
| BLR | -0.82 | -0.77 | -1.31 | -1.45 | -1.46 | -1.60 | -1.77 | -1.75 | -1.70 | -1.59 | -1.55 | -1.56 | -1.66 | -1.57 | -1.54 | -1.44 | -1.47 | -1.34 | -1.36 | -1.37 | -1.41 | -1.45 | -1.58 |
| BLZ | 0.82 | 0.88 | 0.98 | 0.80 | 0.84 | 0.74 | 0.69 | 0.54 | 0.53 | 0.65 | 0.74 | 0.69 | 0.70 | 0.70 | 0.71 | 0.52 | 0.58 | 0.57 | 0.57 | 0.42 | 0.51 | 0.53 | 0.55 |
| BOL | 0.16 | 0.35 | 0.19 | 0.14 | -0.06 | -0.17 | -0.16 | 0.08 | 0.04 | 0.00 | 0.01 | -0.04 | -0.07 | -0.08 | -0.07 | -0.03 | 0.00 | -0.04 | -0.02 | -0.07 | -0.12 | -0.08 | -0.11 |
| BRA | 0.24 | 0.33 | 0.30 | 0.47 | 0.44 | 0.39 | 0.46 | 0.50 | 0.54 | 0.57 | 0.52 | 0.57 | 0.51 | 0.48 | 0.42 | 0.47 | 0.46 | 0.45 | 0.45 | 0.37 | 0.30 | 0.30 | 0.28 |
And I need it to look like this:
| CountryCode | Year | Value |
| ABW | 2018 | 95.75 |
| ABW | 2017 | 95.71 |
| ABW | 2016 | 94.29 |
| ABW | 2015 | 93.33 |
| ABW | 2014 | 92.38 |
| ABW | 2008 | 96.15 |
| ABW | 2013 | 94.79 |
| ABW | 2007 | 96.62 |
| ABW | 2011 | 93.84 |
| ABW | 2012 | 93.36 |
| ABW | 2009 | 88.63 |
| ABW | 2006 | 96.14 |
| ABW | 2010 | 88.15 |
| ABW | 2005 | 96.12 |
| ABW | 2004 | 80.58 |
| ABW | 2003 | 0 |
| ABW | 2002 | 0 |
| ABW | 2001 | 0 |
| ABW | 2000 | 0 |
| ABW | 2020 | 96.23 |
| ABW | 2019 | 95.75 |
| ADO | 2020 | 99.06 |
| ADO | 2019 | 98.58 |
| ADO | 2018 | 97.64 |
| ADO | 2017 | 97.14 |
| ADO | 2016 | 97.14 |
| ADO | 2015 | 97.62 |
| ADO | 2014 | 96.67 |
| ADO | 2013 | 93.84 |
| ADO | 2012 | 94.79 |
| ADO | 2011 | 95.73 |
| ADO | 2010 | 93.84 |
| ADO | 2009 | 94.31 |
| ADO | 2008 | 98.08 |
| ADO | 2007 | 98.55 |
| ADO | 2006 | 98.55 |
| ADO | 2005 | 99.03 |
| ADO | 2004 | 98.06 |
| ADO | 2003 | 98.49 |
| ADO | 2002 | 92.59 |
| ADO | 2001 | 89.68 |
| ADO | 2000 | 86.77 |
Is there an easy way to accomplish this with a function or script? Essentially transpose it, but add a country code value for each Year value on the transpose. Thanks in advance
I´m not sure if this is what you need.. attached is an example and how to do it.
1.- First transform to a table your data.
2.- Then Data > From Table / Range
3.- Inside the power query editor , First select the Country Column > then go to Transfrom > Unpivot other columns.
4.- You are going to have error because of the N/A.
Select the Column Value > Transform > Replace Errors > (then replace for 0)
5.- Go to Home > Close & Load
*If you want to refresh the data, just go to Data > Refresh all like a pivot table.
