Jun 30 2018 02:22 AM
I have an Excel 2016 file with a large set of panel data in the following actual form:
VAR | ISO | VALUE | ||
|
| 1990 | 1991 | 1992 |
IMPORTS | ARG | 1,287 | NA | NA |
IMPORTS | AUS | 9,178 | 5,447 | 10,792 |
IMPORTS | AUT | NA | NA | 1,325 |
GDP | ARG | 153 | 206 | 248 |
GDP | AUS | 323 | 324 | 317 |
GDP | AUT | 167 | 174 | 196 |
I need to transform it to the following desired form:
ISO | YEAR | VAR | |
|
| IMPORTS | GDP |
ARG | 1990 | 1,287 | 153 |
ARG | 1991 | NA | 206 |
ARG | 1992 | NA | 248 |
AUS | 1990 | 9,178 | 323 |
AUS | 1991 | 5,447 | 324 |
AUS | 1992 | 10,792 | 317 |
AUT | 1990 | NA | 167 |
AUT | 1991 | NA | 174 |
AUT | 1992 | 1,325 | 196 |
Can anyone suggest a way to automatically transform the data from the actual format to the desired form:
I thought that a pivot table might work. But I don’t know how to do a multi-level pivot table that would have the ISO and YEAR in the same line.
Any suggestions would be grately appreciated!
Jun 30 2018 03:28 AM
Hi Montague,
Are you on Windows or on Mac? If the first that could be done with Power Query.