Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and you can download and install it for Excel 2010 and 2013:

Click to see tutorial on how to install Power Query in Excel 2013

Click to see tutorial on how to install Power Query in Excel 2010

In Excel 2016 it comes built in the Ribbon menu under the Data tab and within the Get & Transform group.

 

Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within Excel, Power Pivot or the Power BI Designer canvas.

One of the best features is to Unpivot Columns.

What that does is transforms columns with similar characteristics (e.g. Jan, Feb, March…) and puts them in a unique column or tabular format (e.g. Month), which then allows you to do further analysis using Pivot Tables which was not possible before unpivoting.

Here is how this is done:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Highlight your data and go to Power Query > From Table > OK

power query from table

 

STEP 2: This opens the Power Query editor and from here you need to select the columns that you want to unpivot

select column in editor

STEP 3: You then need to go to the Transform tab and select Unpivot Columns

unpivot columns power query

STEP 4: Go to the File tab and choose Close & Load

clos & load power query

STEP 5: This will load and open the unpivoted data into a new worksheet with your Excel workbook.  Now you can go crazy with your super analytical work, using Pivot Tables etc

unpivoted data

Unpivot2

THIS POST WAS INSPIRED BY THE FOLLOWING PODCAST SHOW:

Oz_Podcast_Banner

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Replicating Excel’s FIND Function with M in ... Power Query lets you perform a series of steps to transform your Excel data.  There are times when we want to do things that are not built in the user interface.  This is possible with Power Query's formula language, which is called M.Unfortunately not all of Excel's formulas...
Reverse Rows Using Power Query Power Query lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to reverse the order of rows very easily.DOWNLOAD EXCEL WORKBOOK To provide a quick comparison, it's not straightforward to reverse rows in Exc...
Quick Access Toolbar The Quick Access Toolbar (QAT) is located at the top left hand corner of the ribbon and has the most commonly used commands, like the Save, Undo and Redo.  The QAT is unique to each user´s workbook settings.You can move the QAT below or above the ribbon by right clicking on t...
Show The Percent of Row Total With Excel Pivot Tab... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF ROW TOTAL calculation.This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, exp...