If you have Windows Excel 2010+, you can use Power Query
. It is included in later versions as Get & Transform
on the Data tab, and in the earlier versions as a free add-in from Microsoft.
In the later versions, with a single cell selected in your data table you would navigate to:
Data → Get & Transform Data → From Table/Range
- Ensure the dotted lines encompass only the table, and not any extraneous rows/columns
- The PQ editor will open, showing your original table, and probably a
Changed Type
in the Applied Steps window to the right.
- Sort the
Year
column ascending
- Select the
Year
column, then
- Pivot
- Values Column = KOFGI
- Advanced Options: Don't Aggregate
- Rearrange the
Country Name
and Country Code
columns as you show in your output.
And you're done!
This is the generated M-Code, but will be different on your machine due to a different Table name, and extra columns that you didn't seem to include in your data source
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Country Code", type text}, {"Country Name", type text}, {" Year", Int64.Type}, {"KOFGI", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{" Year", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{" Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{" Year", type text}}, "en-US")[#" Year"]), " Year", "KOFGI"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Country Name", "Country Code", "2010", "2011", "2012", "2013", "2014", "2015", "2016"})
in
#"Reordered Columns"