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
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!
- Introduction to Power Query
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.
- Power Query Data Machine
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!
- 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. (Read more: Excel Charts and Graphs)
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.
- Responsible for general data to be made easy
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 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 Ribbon’s Data tab in the Get & Transform group. (Read more: create a Pivot Table)
- Power Query Ribbon Overview
In Excel 2010 and 2013 for Windows, Power Query is a free add-in. Once installed, the Power Query tab will appear in the Excel Ribbon.
- Power Query is a free add-in
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. (Read more: compare two columns in Excel)
- 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.
- Power Query Editor Window
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.
- The Table in your Excel workbook
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 pivot table’s source. This is also called normalizing your data to get it in tabular format. (Read more: Split delimiters into rows)
The data may start to look like the following.
- Discard data for Pivot Tables
And you want the end result to look like this.
- Discard data for Pivot Tables
Power Query can do this by clicking a few buttons and preparing your data to 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 pull data from different files/sources.
Let’s say you have a folder containing CSV or Excel files with reported data monthly. 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.
- Append (combine) tables with Power Query
If the data in those reports also need 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.
- Merge Tables – An Alternative of VLOOKUP
For example, you have this sales record data table, and you are using VLOOKUP to provide product information based on the product name 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 as 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.
- Create custom function
This new language and set of functions mean there’s a lot to learn, but it’s the fun and challenging part. 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 lots of time if you are preparing the same data every day, week, or month.
- Power Query records your steps and automates processes
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.)
- Power Query Machine & Power BI
You can think of Excel as the factory building that contains all these tools. All technological advances will help us make sense of our data in new ways, save time and impress our bosses.
Source: https://www.excelcampus.com/power-tools/power-query-overview/
Learn More About SEO
If you want to learn more about SEO, check out these other helpful resources!
