Power Query

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
• Filtering Data
• 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 gives the user the ability 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 left. In the right table, 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 which 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.