Key takeaways: Learn how a great Excel and Power BI feature called Power Query helps you automate the import, transformation, and deletion of your data to save your work time.
Skill Level: For Beginners
Table of Contents
- 1 Introduction to Power Query
- 2 Power Query Ribbon Overview
- 3 Example of Data Transformation
- 4 Merge Tables – An Alternative of VLOOKUP
Introduction to Power Query
This tutorial provides an introductory explanation of Power Query. You’ll learn why this is the Excel tool for working with data and how it can help automate processes and save you time!
Power Query Data Machine
We’re watching a TV show about how things are made, and they’re explaining how a deposit machine works in a pastry factory.
The basics of the machine are:
- You add ingredients to the machine.
- Change some settings.
- And the machine that magically creates pastries (cookies, donuts, etc.) is ready to bake.
Once the parameters are set, the process can be repeated several times to create the perfect pastries each time.
Power Query works in a very similar way!
- You add your data source (Excel table, CSV file, database table, web page, etc.)
- Press the buttons in the Power Query Editor window to transform your data.
- Export that data to your spreadsheet or data model (PowerPivot) ready for pivot tables or reports.
Power Query is like a machine because once you’ve set up your query, the process can be repeated with the click of a (refresh) button each time your data changes.
If you’ve used macros to transform your data, you might think this is a much easier alternative to VBA that doesn’t require coding .
Responsible for general data to be made easy
Do you work with data that has been exported from a records system? This could be a general ledger, accounting, ERP, CRM, Salesforce.com, or any reporting system that contains the data.
If so, you could spend a lot of time transforming or reshaping your data to create additional reports, pivot tables, or charts.
These data transformations can include tasks such as:
- Delete columns, rows, blanks
- Convert data types – text, numbers, dates
- Split or merge columns
- Sort and filter columns
- Add calculated columns
- Summarize or summarize data
- Find and replace text
- Discard data to use for pivot tables
Do any of these missions sound familiar? If so, then they probably also sound boring, repetitive, and time-consuming.
Luckily, Power Query has buttons that automate all of these tasks!
Power Query Ribbon Overview
Starting with Excel 2016 for Windows, Power Query has been fully integrated into Excel. It’s currently on the Ribbon’s Data tab in the Get & Transform group.
In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will show up in the Excel Ribbon.
You use the buttons in the Data or Power Query tabs to get your source data. Again, your data can be stored in Excel files, csv files, Access, SQL server database, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, websites, etc.
Once you’ve specified where your data comes from, you then use the Power Query Editor window to perform the data transformation.
The buttons in the Power Query Editor Window allow you to transform your data.
Think about some of the tasks you perform repeatedly as you browse the buttons in the image above. Each time you press a button, your actions (steps) are recorded and you can quickly reapply the steps when you get new data by refreshing the query.
After completing your steps, you can export the data to the Table in your Excel workbook by clicking the Close & Load button.
You can also modify existing queries and refresh your output tables with changes or updated data.
Example of Data Transformation
Here are a few examples of what Power Query can do with your data.
Discard data for Pivot Tables
My favorite feature about Power Query is the ability to Unpivot data. This is a technique used to make your data available to the source of the pivot table. This is also called normalizing your data to get it in tabular format.
The data may start to look like the following.
And you want the end result to look like this.
Power Query can do this by clicking a few buttons and preparing your data for use in the pivot table.
Append (combine) tables with Power Query
Power Query’s Append feature allows you to combine multiple tables (stack them vertically) to create one large table. It can do this with multiple tables in a single file, or it can pull data from a bunch of different files/sources.
Let’s say you have a folder containing CSV or Excel files with report data for each month. Feed all those files into the Power Query machine and it will produce a nice table that you can then use to create tables and charts.
If the data in those reports also needs to be transformed (delete rows, split columns, unpivot, etc.), Power Query can handle that in the same process.
Once it’s set up, all you have to do is hit the refresh button every month when a new file is added to the directory and rows will be added to your output table.
Merge Tables – An Alternative of VLOOKUP
Power Query has the ability to merge or join tables. This can be used in place of VLOOKUP or INDEX/MATCH formulas.
For example, you have this sales record data table and you are using VLOOKUP to provide product information based on the name of the product being sold. Your product group information is placed in a different table on a different sheet or workbook.
Using the VLOOKUP functions is great, but it often means adding thousands of formulas to your workbook. Increases file size and computation time.
Power Query makes merging two tables very quick and easy with just a few clicks. It basically uses SQL joins so you can even do more advanced merges like inner, outer, left, right, full and anti-join joins .
Create custom function
Power Query is designed so that you do NOT have to know how to code to use it. It is very easy to use because you just need to click a button and apply filters like you would normally do in Excel.
However, Power Query can be programmed to create custom functions. This gives you seemingly unlimited potential to transform your data in any way possible.
It is based on the M language and most of the functions are very similar to writing formulas in Excel. This also makes it more user-friendly and easier to learn the code.
This new language and set of functions mean there’s a lot to learn, but it’s the fun and challenging part. Plus, future employers are sure to look for employees with Power Query skills .
Power Query records your steps and automates processes
Power Query not only makes all these tasks easier, but it also records your steps so you do NOT have to do them again and again . It will save you a lot of time if you are preparing the same data every day, week or month.
It also handles errors pretty well. If the structure of your source data changes, Power Query will tell you which step is broken and allow you to fix it. This makes maintenance easy and you don’t have to redo your process when something changes
You can use Power Query to have your data ready in pivot tables, charts, and dashboard reports. This is an important step in data summarization and analysis.
Power Query Machine & Power Query BI
Power Query is just one part of Microsoft’s Power BI (Business Intelligence) product suite.
If we go back to the factory-baked cookie analogy, you might think of Power Query as the first step in the assembly line. Once cookies are formed, we then need to bake them (Pivot Tables, PowerPivot) and then package them for presentation (Power View, Power Map, Charts, Dashboards, etc.)
You can think of Excel as the factory building that contains all these tools. All these advancements in technology will help us make sense of our data in new ways, save time and impress our bosses.