Power Query is a tool used to manipulate data and is commonly used for data analysis and business intelligence.
High-quality analysis relies on well-organized data that is free from errors and that draws data from a single source of truth. Whilst many analysts spend hours of time manipulating data, combining data, and performing look ups, Power Query allows all of these steps to be automated with simple, repeatable steps.
Power Query is one of the most transformative tools available to any analyst working with data in Excel. Not only will it save analysts hours of time, but it will result in reduced manual errors and a better ability to source data from a single source of truth.
Power Query is a data transformation tool available in both Microsoft Excel and Power BI.
Power Query is an invaluable tool for any analyst working in Excel, saving users hours of time, reducing manual errors, and allowing users to source data from a central source of truth.
Power Query remembers the user’s data transformation steps, effectively automating lengthy and manual processes.
Common use cases are to modify the layout of data, connected to central data sources, merge related tables, or combine multiple files.
Top Uses/Benefits of Power Query
1. Transforming Data
The core purpose of Power Query is to transform data. This means modifying the layout, removing errors, and generally making the data more usable for analysis.
Common data transformations performed in Power Query include:
Pivot / Unpivot
Splitting / Merging Columns
Creating custom columns
Deleting header / blank / error rows
Changing data types
Fill down values
Example: In this example, a user wants to calculate the monthly performance of each store. The problem is that the data in their text file looks like this (below).
This type of data will be very familiar to Excel users, who know just how much work is involved in re-formatting data to fit their analysis needs.
There are unneeded rows at the top of the file, columns of blank values, values to fill down, and dates to unpivot.
With a few simple steps, Power Query allows the user to transform the data into the below format. And importantly, the process probably took about a minute to complete.
2. Repeatable Steps
If the above example isn’t enough, Power Query also remembers each of the steps that were taken to transform the data. This means that when reports are refreshed or changes are made, model inputs can be refreshed in seconds, with no need to re-do the whole process from scratch.
Example: In this image, we can see the list of steps that have been applied to the dataset. Each time we update the data source, the steps are applied and the outputs are updated.
3. Combining Multiple Tables
Power Query allows us to combine multiple tables of related data. Excel users would achieve the same task using lookup formulas. They’d be limited to 1 million rows and find their models became increasingly slow with the addition of so many formulas.
Instead, Power Query joins the tables efficiently using matching columns, does not require any formulas, and is achieved for the entire dataset in seconds.
Example: In this example, we have a table of transactions on the top. In the second table below, we have some supporting information about the delivery of each transaction.
With just a couple of clicks in Power Query, we can use the matching columns to merge the two tables and add the delivery information to the original table.
4. Combining Multiple Files
Power Query includes functionality to seamlessly combine data from multiple similar files.
Example: Suppose we have three similar files, each containing sales data for our stores in a given month. Our model requires a single file with a consolidated list of transactions.
With Power Query, we can easily combine the above files into a single table as shown below. This can save analysts huge amounts of time and manual error risk.
5. Connection to Central Data Sources
Power Query includes many connectors that allow you to pull data from SQL databases, web pages, cloud storage, and local files. By always referencing central sources of data, analysts can ensure they are all working from the one version of the truth.
Using the Get Data menu, it’s easy to customize queries to source data from each data source type.
Anybody working in Excel will drastically improve their ability to work with and manipulate data, by working with Power Query. Instead of manually updating models every week or month, analysts can now automate inputs and focus on their work.
2. Analysts interested in Business Intelligence
Transforming data for projects is an essential step in the business intelligence process (see below). For that reason, Power Query is a great place to start for any Excel-based analyst interested in Business Intelligence. It makes use of the familiar Excel environment, provides transferable skills into Power BI, and is incredibly easy to use.
3. Anyone working with data
Even if Excel or Power BI is not your primary BI tool, Power Query is perfect for playing around with data or quickly combining files for other projects. Load the outputs into a worksheet and use it to quickly prototype a BI project with clean data.
Where Can I Find Power Query?
Power Query is available in both Microsoft Excel and Microsoft Power BI.
Power Query in Excel
Since Excel 2016, Power Query is available natively. If you have an earlier version of Excel, you may have to download it as an add-in.
In recent versions, Power Query can be accessed via the Data tab in Excel and consists of several buttons in the Get & Transform Data section.
Power Query in Power BI
Power BI is Microsoft’s market-leading dashboarding tool, which allows users to import and transform data, create a data model, and create interactive dashboards.
Power BI makes use of the same Power Query technology to perform all the required data transformations. It can be accessed via the Data and Queries sections of the Home tab.