Site logo white shadow

How to use Unpivot to clean data instantly

This is a log of what I explore and learn.

Power Query – Unpivot Other Columns

Earlier I wrote an article about the Unpivot feature of Power Query:
Crosstab data consolidation using Power Query

The concept is simple but powerful. Consider the data… It is already imported into Power Query. This is a cross-tab. I want simple tabular data containing three columns: Country, Year and Consumption.


This is done by selecting all the year columns and then right click to choose the UNPIVOT option. Recently, Power Query team updated few features. Unpivot was one of them.

The data I have is from 1965 to 2012. Imaging the trouble you have to take to just select so many columns. Therefore, the Power Query team smartly added a new feature called Unpivot Other Columns. Note that this option is available in the drop down menu on the Transform ribbon. It is NOT available in the right click menu (context menu).

Power Query unpivot other columns

Now what do I need to do? Just select the First column and choose this option. Job done. Now we got data like this. (I have renamed the first column as country now).


Creating this information into a 3D map

Just to complete this topic, I will show you the output of this data shown using Power Map. In the upcoming articles, I will discuss Power Map in detail.

Data visualization 3D map


Queries | Comments | Suggestions | Wish list

Subscribe to Blog

Join 1,761 other subscribers

Popular articles

Pivot Table Pro Course

Yes. You use Pivot Tables everyday. Now it is time to find out the real power and nuances. 5.5 hours video, exercises, samples, Q&A.

Excel to Power BI Course

Learn Power BI using the concepts you already know in Excel. Fast transition, in-depth coverage and immediately usable.