Need to Transpose Countries over Years, For Each Country List All Year Values

Copper Contributor

Hi. Basically I have data that looks like this: 

 19961998200020022003200420052006200720082009201020112012201320142015201620172018201920202021
ABW#N/A#N/A#N/A#N/A#N/A0.741.151.020.970.971.251.281.271.281.281.281.271.281.301.271.261.291.03
ADO1.561.531.541.441.421.561.531.331.311.331.321.321.361.431.411.171.191.181.161.041.111.091.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.010.070.010.000.080.110.170.140.120.060.020.050.140.160.170.200.180.140.090.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
ARG0.390.310.420.260.350.360.270.400.450.360.280.360.340.300.280.350.410.490.530.530.570.590.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.140.060.050.06
ASM#N/A#N/A#N/A#N/A#N/A0.500.650.550.970.970.960.991.011.031.00#N/A#N/A#N/A#N/A1.461.271.060.91
ATG0.330.340.390.340.320.570.530.630.540.570.590.500.510.640.620.560.600.630.610.570.730.750.74
AUS1.441.411.471.411.441.501.511.381.371.371.381.421.451.501.441.361.361.351.381.381.271.301.38
AUT1.451.351.321.311.341.481.381.371.371.361.391.431.401.451.461.391.381.341.391.391.341.401.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
BEL1.431.341.371.381.481.441.421.341.361.321.351.361.331.351.371.371.391.381.341.321.311.281.28
BEN0.260.430.410.050.190.05-0.030.360.370.340.340.310.170.120.160.260.290.410.380.240.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.110.010.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
BGR0.430.440.460.530.550.570.590.580.690.580.560.530.450.400.340.370.430.400.430.360.360.260.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
BHS1.101.161.201.191.030.980.941.021.101.121.010.990.960.930.921.050.950.850.850.760.910.920.91
BIH-0.10-0.01-0.11-0.090.210.170.210.190.130.020.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
BLZ0.820.880.980.800.840.740.690.540.530.650.740.690.700.700.710.520.580.570.570.420.510.530.55
BOL0.160.350.190.14-0.06-0.17-0.160.080.040.000.01-0.04-0.07-0.08-0.07-0.030.00-0.04-0.02-0.07-0.12-0.08-0.11
BRA0.240.330.300.470.440.390.460.500.540.570.520.570.510.480.420.470.460.450.450.370.300.30

0.28

 

And I need it to look like this: 

CountryCodeYearValue
ABW201895.75
ABW201795.71
ABW201694.29
ABW201593.33
ABW201492.38
ABW200896.15
ABW201394.79
ABW200796.62
ABW201193.84
ABW201293.36
ABW200988.63
ABW200696.14
ABW201088.15
ABW200596.12
ABW200480.58
ABW20030
ABW20020
ABW20010
ABW20000
ABW202096.23
ABW201995.75
ADO202099.06
ADO201998.58
ADO201897.64
ADO201797.14
ADO201697.14
ADO201597.62
ADO201496.67
ADO201393.84
ADO201294.79
ADO201195.73
ADO201093.84
ADO200994.31
ADO200898.08
ADO200798.55
ADO200698.55
ADO200599.03
ADO200498.06
ADO200398.49
ADO200292.59
ADO200189.68
ADO200086.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

1 Reply

I´m not sure if this is what you need.. attached is an example and how to do it.

@michaelterenzi 

 

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.