Power Query Problems

Unpivot Country and Year Data into Three Columns in Excel

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Convert country codes and year columns into a normalized table with CountryCode, Year, and Value fields, while handling error values.

Unpivot Country and Year Data into Three Columns in Excel
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A worksheet has country codes in rows and separate columns for each year.
Observed behavior
The desired result is one row per country and year, with the corresponding value in a third column.
Before you start

Check that the source range has a header for the country code and a separate header for each year. Remove unrelated columns or identify them before unpivoting, since Unpivot Other Columns transforms every unselected column.

Solution 1Recommended

Unpivot the year columns with Excel Power Query

Use this method to create a refreshable three-column table from the source data.

1
Create a source table

Select the country and year data in Excel, press Ctrl+T, confirm that the table has headers, and select OK.

2
Open Power Query

Click a cell in the table and select Data > From Table/Range to open the Power Query Editor.

3
Unpivot the years

Select the country-code column, then choose Transform > Unpivot Other Columns. Power Query creates Attribute and Value columns.

4
Name the output columns

Double-click the column headers to name them CountryCode, Year, and Value. If needed, select Year and use its data type icon to set it to Whole Number.

5
Handle errors

If the Value column contains errors such as #N/A, select Value and choose Transform > Replace Errors. Enter 0 only if zero is an appropriate substitute for the missing or invalid value.

6
Load and refresh

Select Home > Close & Load to return the result to Excel. After changing the source table, select Data > Refresh All to update the output.

Unpivot the year columns with Excel Power Query
Check missing values: Power Query unpivoting omits null values, so blank source cells may not produce rows. Replacing errors with 0 also changes their meaning; use it only when appropriate for your analysis.
Free Microsoft Office alternative

Work with country and year spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface and Excel format compatibility for an easier move between spreadsheet tools. Use WPS Spreadsheets to review and edit country and year data; it does not replace Excel's Power Query unpivot and refresh workflow.

Review and edit country, year, and value columns in WPS SpreadsheetsOpen and save Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface to ease migration from ExcelWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

How do I keep CountryCode from being unpivoted?

In Power Query Editor, select the country-code column before choosing Transform > Unpivot Other Columns. The selected column stays in place while the year columns are transformed.

Why are some country and year combinations missing from the result?

Power Query unpivoting omits null values. Check the corresponding source cells for blanks and decide how missing values should be represented before unpivoting.

Should I replace #N/A errors with zero?

Only replace them with zero if zero is meaningful for your analysis. To do so, select the Value column in Power Query and choose Transform > Replace Errors.

How do I update the unpivoted table after adding data?

Add the data to the Excel source table, then select Data > Refresh All. Power Query reruns the transformation and updates the loaded result.