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!

Pivot-Table-for-Customer-Country-Channel-and-Product How To Unpivot Data With Power Query

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.

Original-Unpivoted-Source-Data How To Unpivot Data With Power Query

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

Create-Table-Query-for-Data-to-Unpivot How To Unpivot Data With Power Query

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-Columns-in-Query-Editor-to-Unpivot How To Unpivot Data With Power Query

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.

Unpivot-Only-Selected-Columns How To Unpivot Data With Power Query

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

Query-Editor-Preview-of-Unpivoted-Data How To Unpivot Data With Power Query

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.

Close-Load-Unpivoted-Data How To Unpivot Data With Power Query

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.