Unpivoting Data in Power Query

February 21, 2018 - by Bill Jelen

Unpivoting in Excel solves something that was a 200-keystroke nightmare and makes it be just a few clicks.

Today, another tip from Excel project manager Ash Sharma. Say that you have thousands of rows of data as shown in columns A:N in the figure below. You have two columns of labels down the side and then months of data stretching across the top. It isn't fun to create pivot tables from data like this. As Rob Collie of PowerPivotPro teaches, your data should be tall and thin instead of wide. Instead of 14 columns of 20 rows, you need 4 columns of 240 rows.

I've solved this problem for years. With 200+ keystrokes. Copy, paste, copy, paste. Here is a 13-second rendition, created by speeding up the video 100%:

There were other ways. Rocket scientists could use Mike Alexander's Multiple Consolidation Range Pivot Table trick. But none of that is necessary any more.

Instead, you can unpivot the data in 10 keystrokes. First, a 30-second video, then I will describe the 10 keystrokes.

• Step 1: Select one cell in your data
• Step 2: Click on the Data tab in the Ribbon
• Step 3: In the Get and Transform Data group, choose From Table/Range
• Step 4: Click OK as Excel guesses the extent of your table. Power Query opens and the Product column is selected.
• Step 5: Ctrl + Click on the Market heading to select both columns
• Step 6: Click on the Transform tab in the Power Query ribbon
• Step 7: Open the tiny dropdown next to Unpivot Columns
• Step 8: Choose Unpivot Other Columns
• Step 9: Click on the Home tab in the Power Query Ribbon
• Step 10: Click on Close and Load. In a few seconds, Excel will insert a new worksheet with the newly formed data.

It took 30 seconds in the video to do it once.

But let's compare and contrast those two videos. If your manager sends you new budget numbers the old way, you will have to do the 200 clicks again. It is mind-numbing.

But with Power Query, copy the new data, paste, and click Refresh All.

How did these features sneak into Excel without anyone knowing they are there? That is a great question. If you have Excel 2010 or Excel 2013 for Windows, you might have downloaded the Power Query add-in for Excel. By the time they were added to Excel 2016, many Excel gurus thought they were old news.

I love to ask the Excel team for their favorite features. Each Wednesday, I will share one of their answers.

Title Photo: Brent De Ranter / Unsplash