What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration.
If you haven’t heard of Excel’s Power Query tool, or you’ve heard of it but you’re not sure if it’ll be useful to you, then check out the video below where I showcase what the fuss is all about.
Download Example Files & Cheat Sheet
Enter your email address below to download the files and cheat sheet.
Watch the Video
Where is Power Query
Power Query is a tool built by Microsoft for Excel and Power BI. It’s the same tool in both apps, so you only need to learn it once and you can use it in either app.
In Excel the tools are in different places depending on the version of Excel you have:
Excel 2010 and 2013 users download Power Query here.
Click here to find Power Query in your version of Excel.
Power Query's purpose is to:
|Automate the laborious getting and cleaning data tasks.
|Eliminate the need to be a programmer: traditionally if you wanted to automate these laborious tasks, you’d use Excel’s programming language, VBA. However, Power Query doesn’t require programming knowledge as most of its tools are available from the GUI with point and click ease, as you can see in the video.
|Reduce time-consuming tasks that can take hours, down to a fraction of the time, sometimes as little as a few seconds.
Once you’ve gathered and cleaned your data using Power Query, you’re ready to use tools like formulas, PivotTables, and Power Pivot to analyse and visualise the data.
Power Query can get data from almost any file source imaginable, including proprietary systems that have either OLEDB or ODBC drivers. The image below lists the connectors currently available in Excel, but they're adding more all the time.
Power BI has even more sources available, including loads of online services. See the full list here.
Built-in Data Cleaning Tools
There is a vast range of built-in data cleaning and transformation tools readily available from Power Query's ribbon. Below are screenshots of the main tabs, but the menus don’t do its power justice. If you haven’t seen the video above, take a few minutes to see how easy it is to use.
For those keen to take things to the next level, there is also an extensive function library.
If you’re keen to get up to speed quickly, please consider my comprehensive Power Query course.