How to Use Power Query for Pivot Table Data Analysis

by Matthew Kuo on March 13, 2016

in Database Theory, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the

The following is a guest post from , co-founder of , a site that helps working professionals become faster and better in Microsoft office suite using high-quality .  Before founding Yoda Learning, Rishabh worked with an Education-focused Private Equity fund in Mumbai, India.  In addition to being an expert, Rishabh is an avid traveler and a music enthusiast.

Automated data transformation is one of the most popular trending topics these days.  It can involve several different methods and techniques of automatically processing data without involving any human interaction.  Automated data transformation has several advantages, which are the main driver behind its growing popularity.  Two of the most common reasons are listed below:

  1. Lower risk of errors – Earlier, when human beings had to manually perform these tasks, discrepancies would often occur since it would be easy to make a data processing error.  As a result, companies often suffered huge losses.
  2. Significantly increased efficiency – Computers are obviously much faster than human beings at data manipulation, and are extremely efficient as well.  Automated data processing is completely essential if you have to process huge amounts of data.

Let us consider a small example of automated processes in real life.  Let’s say you have a microwave oven and you want to prepare pizza.  You can just prepare all the ingredients, insert them into the microwave oven, press the automatic recipe maker, set it to “pizza,” and you are done!  If you want to prepare any other food item, which is already present in the automatic recipe maker, you just need to set it to that food item, and once the ingredients are ready, it will take care of the rest.

Automated processing is very similar.  But how do you get started with automated data transformation?  Especially if you have been using Excel for all your activities?  Fortunately, there is a similar feature in  known as Power Query.

What Is Power Query and How To Install it

Power Query is a tool which makes the processes of data discovery and access much easier by improving your Business Intelligence experience.

Power Query is works with Microsoft Excel and is available for free.  You do however, need to install it separately.  Check the following link –  – and download the appropriate add-on, and be sure to designate the right Excel version, as well as whether your computer is a 32-bit computer or a 64-bit one.

Please note that if you are using Microsoft Excel 2016, Power Query has been renamed to “Get & Transform”, although the features are the same.  Also, you no longer need to download and install this add-on separately, since it is already pre-installed.

How To Use Power Query To Build Pivot Table

Power Query 03

is one of the most powerful features of Excel.  It is frequently used since it allows the user to understand and recognize patterns in a data set.  However, you can’t use it properly if your data is not in a specific format.

Let us consider an example. Suppose we have a dataset as shown below.

Building Pivot Table from this dataset will be cumbersome since it has not been set up in a specific format.  It would be best if the dataset was arranged setup shown below.  But manually performing this task would take a lot of time. Fortunately, using Power Query you can automate this process.

For a thorough explanation of this process, I suggest that you to watch the following video created by (also a co-founder of ) which shows the solution in the simplest way possible:

Follow the steps given below to change the format of the dataset:

  1. Click on Power Query and select the source of your data.  If your data is already present in the Excel file, you need to select “From Table”.  Otherwise, you have the options of choosing the data from csv files or other sources.
  2. Usually, you will find that the first row is being used as headers. E.g. in the above example, Departments, months, etc. should be used as headers.  In case, they aren’t being used as headers, you can just click on Transforms and then the option “Use First Row as Headers”.
  3. Now you need to choose those columns which you want below one another.  Here, we want the months in that format, so just choose the columns “Jan” to “Dec”.
  4. Next, click on Transform and then “Unpivot Columns”.
  5. Your data have now been changed.  If you don’t prefer this change, you can simply undo the steps as well.
  6. Now you have to save this data set.  Click on File and “Close and Load To”.  Create a new worksheet and save the modified data set.

Your data is now ready for use in a Pivot Table.  Now, you can easily create a Pivot Table from the Insert Menu and choose whatever attributes you want in your table for easy comparison and analysis of the data set.

Hopefully, this tutorial has demonstrated how easy it is to start working with Power Query.  This same job, if done manually, would have taken at least an hour for a moderately large dataset.  Therefore, when faced with a significant data manipulation task, make sure you consider using Power Query as it has the potential to reduce your workload significantly.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post:

Related pages

excel formula lottery number generatorwhat is the countif function in excelvlookup tutorial excelexcel graph pointswhat is a bin range in excelindirect function excel 2013ifs statement excelucla luvalle bookstoredelete duplicate values excelwhy vlookup is not workinghow to generate random numbers in excel without duplicatestick on excelpassword generator excelremoving duplicate cells in excelv look up exampleshort cut for filter in excelexcel multiple if statementhlookup google spreadsheetdrag cell in excel not workingstandard deviation vbastacked graphs exceldivide numbers in excelvlookup across sheetslookup value in excelduplicate cells in excelexcel swap columnshow to delete macros in excellogic formula in excelhow to draw a normal curvehow to draw a circle in excelhow to drag down a formula in excelexcel index match multiple resultsawesome excel templatesms excel functions with examplescpk formula for excelcomparing 2 columns in excelinsert macro in excelstudent pickerformat excelexcel if then formula with multiple conditionshow to learn formulas in excelexcel insert row with formulahow to find duplicate rows in excelexcel formula to remove duplicatestrend graph in excelbar graph in excel 2007excel hide duplicatesgraph distribution excelrandom number selector excelexcel comparison formulaexcel 2010 column headershlook upaverage gpa uclavba excel macro tutorialbar diagram in exceldifference between vlookup and hlookupwhat is the newest version of excelstacked bar graphshighlight column excelassignment trackerhlookup formula in excel 2010excel useful shortcutsvlookup if statementvlookup meaningucla mba calendarmultiple if then statements in excel 2010vba chart axishow to draw a straight line in excelvlookup and if statementbest buy downsizingcreating histogram in excelcompare 2 columns in excel for differencesfinding duplicate values in excelucla anderson deanhow to remove duplicates in excel 2010how to create formulas in exceluse of excel formulas with exampleyou can identify duplicate rows in a table using thevlookup code in vba