What is Power Query

Level: Beginners

Edit: Sep 2019

I am still astounded by the number of people I meet that have never heard of Power Query and Power Pivot.  The good news is there are lots of people out there in for a pleasant surprise.  I think Power BI will solve the “lack of awareness of Power Query” problem over the coming months and years.  Power BI is much more visible as can been seen in this Google Trends chart that I first created in Dec 2014.

I have written a blog titled “What is Power Pivot” before and I refer people to that page all the time – this helps me quickly give people a detailed summary of what they need to know.  Today I am writing about Power Query for the same reason – to have something that I can point people towards when they are just starting out.

So What is Power Query?

Power Query is an Extract, Transform and Load tool (ETL) that helps Power BI and Excel users connect to data, transform the shape of the data as required and then load the data for further use, typically into Power BI,  Excel, CSV etc.  ETL tools are not new – there are many enterprise strength tools in the marketplace that are used by IT professionals.  What makes Power Query such a ground-breaking tool is it is the first tool built specifically for business users.

Who can benefit from Power Query?

If one or more of the following scenarios applies to you, then Power Query is a great tool to make your life better.

• You spend hours and hours of repetitive work combining data from various source into a single data table before you can start the real work.
• You have data in a file but it is the wrong shape for the task at hand.  You need to spend time “reshaping” the data before you can use it.
• You write lots of VLOOKUP formulas in Excel over multiple tables of data to “join” the data together.
• You manually have to “cleanse” your data every month removing data and errors that you already know about but you can’t permanently solve back at the source.
• You need to get data from web pages into Power BI so you can analyse the data. You cut and paste the data and spend hours reformatting it to remove the noise from the data.

What Can Power Query Do?

Power Query can be used for the following (and probably many more things too).

• A data loading tool
• A data cleansing tool (remove unwanted columns, rows, characters etc)
• A data reshaping tool including a very powerful “unpivot data” function (example shown below)

• A tool to combine data from multiple worksheets or workbooks into a single file/table

• A tool to join data from different sources into 1 coherent data set or table.
• An auditing tool – compare lists of data and find the similarities and differences.
• A website data scraping tool – easily load tabular data from a web page into Power BI or Excel .
• A data creation tool (e.g. it can create a calendar table for Power BI).  Read my blog posts about how create a regular Calendar table here, or a 445 Calendar table here.

What Makes Power Query So Great?

Power Query is great for many reasons including:

• It has a simple User Interface that any business user can learn.  Note the simple menu driven features in the image below.
  
• The user interface is built on top of a very powerful formula language ‘M’ that has immense capabilities well beyond what most users will ever need.  If you do ever need something more complex than the UI can support, the language is there (as shown below) and can be used to build a working solution without switching tools.
  
• Power Query records repeatable “Process Steps” that can be reused over and over without destroying the underlying data.  You can go back through the Process Steps and see the data before the transformation took place.  You can even insert new transformation steps earlier in the process flow if needed.
  
• Repeatable process steps means that you can build the steps for one set of data and then you can “reapply” those same process steps to a different set of data (provided both sets of data are fundamentally the same “shape”).  This is great for updating weekly or monthly data (for example).
  

Versions of Power Query

Power BI Desktop

Power Query is fully integrated into Power BI Desktop where it is called “Get Data” – unfortunately.  You can find it by navigating to the Home Tab (#1 below) and then clicking Get Data (#2 below).  Power Query (Get Data) is the only way to load data in Power BI Desktop, so if you have used this tool to build a new workbook then you have already used Power Query (Get Data).

Why Microsoft couldn’t stick with the name “Power Query” is anyone’s guess.  Maybe they thought users were “scared off” by the “Power” term – who knows.

Excel 2016

In Excel 2016 also the product was fully integrated into Excel and renamed “Get and Transform” – unfortunately.  To find it in Excel 2016, you need to go to the Data tab (#1 below) and then you will see the Get and Transform group (#2 below).

Excel 2010/2013

Power Query was first released as a free Addin for Excel 2010 and Excel 2013.  After you install the Addin, you get a new menu on the Excel Ribbon shown below.

You can find the download details for Excel 2010/2013 on my downloads page here.

The Power Query Formula Language (‘M’)

The official name of the underlying language of Power Query is called “M”.  The intellisense in ‘M’ is not as great as DAX but Microsoft is improvising it regularly.

Where to learn more

If you want a comprehensive lesson on how to use Power Query, checkout my online training course here Power Query Online Training for Excel & Power BI Professionals.

I often blog about how to get value from Power Query.  You can subscribe to my blog via email at the top of this page.  You can also explore the previous Power Query blog posts that I have written here.

There is a great book by Ken Puls and Miguel Escobar – I wrote a book review here.  I am also a big fan of Ken’s blog that you can find here.