How To Unpivot Data With Power Query

2017-11-20

This situation happens a lot. Someone creates a pivot table based on some source data. They copy and paste it as values and that becomes your new data source. But really you would have preferred the unpivoted source data.

Can you easily unpivot the data? Yes!

I have created a pivot table based on some source sales data.

  • Rows area – I’ve added Customer Name, Customer Country and Sales Channel.
  • Column area – I’ve added the Product sold.
  • Values area – I’ve added the Total.

We can see this creates a new set of data where each product has its own column heading. This is pivoted data.

This the original source data. It’s in an unpivoted format. We want to go from the pivoted data to the unpivoted data.

Select your data to unpivot (which has been pasted as values from a pivot table) and go to the Data tab in the ribbon and press the From Table/Range button.

Select the columns to unpivot in the query editor. These are all the product columns.

  1. Left click on the first column heading.
  2. Hold the Shift key.
  3. Left click on the last column heading.

Right click on any of the selected columns and select Unpivot Only Selected Columns from the menu.

The query editor preview will show the unpivoted data. You can rename the columns from the default Attribute and Value to something more relevant with a double left click on the heading. In our case these should be Product Sold and Total.

Go to the Home tab in the query editor and press the Close & Load button to save the query and output the resulting data to a new sheet. This is a very quick way to reformat your data back to a more usable tabular format.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest