Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal

What is Excel Power Query?

Using Power Query in Microsoft Excel opens up the possibility of unlocking invaluable Business Intelligence for managers, which is exactly what we’re going to cover today. But first thing’s first: What is Power Query and where can you access it?

What is Power Query?

Excel Power Query allows users to connect to a myriad of databases, anything from a CSV file to SQL Server or Oracle, or even a table on a web page. The best part? You do not need technical knowledge of how to connect to a data source, and the connections can be made using a few simple clicks right in Excel. You also don’t need any knowledge of how to write a query on the data as this is all done using the Power Query Interface. In other words, this is an extremely powerful function.+

You can access Power Query on the Data tab of the Excel ribbon.

How can I use Power Query as a manager?

1. First of all, you can extract data from many other sources such as a web page, a database (like SQL Server or Oracle), or even just another Excel spreadsheet.
2. You can transform this data by joining sources together, removing columns that are not needed, and adding calculated columns, until you have the results in the exact format you want.
3. You can then present these results in the form of charts or as a pivot table in order to highlight important points for future discussion.
4. Report automation is another thing you can do with Power Query. Want to see it in action? Here’s a tutorial on how to build an automated time tracking dashboard in Excel.

Before we get started, some prerequisites for these hacks.

In some of the examples below, I’ve created sample data in order to demonstrate the capabilities of Power Query. It is assumed that these have already been loaded into Excel Power Query from a separate Excel spreadsheet.

Sales Data (1)

Sales Data (2)

Price Data

Sales Detail

5 Business intelligence hacks using Excel Power Query

1. Find unmatched items between two data sources

An example of this is where there are two data sources, one being maintained locally, and one being maintained centrally. The local one may simply be an Excel spreadsheet, and the central one may be a SQL Server database. These may hold, for example, sales data, but the two data sources do not agree. Power Query allows the user to quickly find out the missing items and resolve any differences, obviously saving one of your most valuable resources—time.

In the sample data, Sales Data (1) is different to Sales Data (2). Some items match, but there are some in each data source that do not match the other. In this case, the unmatched items are easy to see because the data sample is very small, but at work you’d most likely be working with a much larger data set.

If each source contains a huge number of rows, it would be a difficult task to find the individual items in one source but not in the other. That is where Power Query comes in. Using Power Query, you can find the unmatched items quickly.

Select Get Data on the far left of the ribbon and then click on Combine Queries. Select Merge, and a pop up screen will appear to allow you to select the two data sources to be compared.

Choose Sales Data (1) from the drop down for the first table and Sales Data (2) for the second table.

Click on the Product column in each table to show that this will be used to join the two tables together. These will be highlighted.

The Join Kind drop down shows a number of options. Choose Full Outer. Your screen should now look like this:

Click on OK and the query will be created.

The query result will need expanding—click on the arrow symbol in the right of the Sales Data (2) header to see this screen.

Click on OK ,and this will show the full detail of the query result. Where rows do not match you can see null values are shown.

Click on OK, and then select Close & Load from the far left of the ribbon.

The data will be loaded into a new sheet, and the null values will be shown as blanks e.g cells A4, D6:

This shows a comparison between two tables, Sales Data (1) and Sales Data (2). They have identical column names of Product and Sales.

You can see that tiles and plasterboard exist in Sales Data (2), but not in Sales Data (1). Conversely, Bricks exists in Sales Data (1) but not in Sales Data (2).

You can now use the filters in the column header to isolate the blank values so as to show what is in one table and not in the other. Note that the query will automatically be called Merge1, but you can change the name.

2. Merge more than two data sources to give one result

It is usual in a relational database for data to be held across several different tables. For example, one table may hold customer information, another table may hold orders that the customer has placed, and a further table may hold details of the actual products that were ordered. Or perhaps you’d like to combine your employee time tracking and timesheet data with information from other sources. Whatever your case, Power Query can help.

These tables are all linked by a unique ID, but to report anything meaningful from this, the tables have to be joined together to form one query.

On the unmatched query above, you may want to add pricing data to it. The Merge screen only allows for two tables or queries, so how do you bring in pricing data?

The answer is to create another query based on Merge1 and Pricing Data.

Select Get Data on the far left of the ribbon and then click on Combine Queries. Select Merge, and a pop up screen will appear to allow you to select the two data sources to be compared.

Select Merge1 for the first table and Price Data for the second.

Click on Sales Data (2).Product in Merge1 and Product in Pricing Data to indicate the columns to be joined. Your screen will look like this:

For Join Kind choose Full Outer.

Click OK and the query result will be shown.

Expand the Price Data columns by clicking on the arrow symbol in the header (check the previous tip if you’re unsure here.)

Select Close & Load from the far left of the ribbon, and a new sheet will be created with the results of three different data sources:

Note that Bricks is shown in Price data as orphaned because we joined on Sales Data (2).Product and Bricks was missing from Sales Data (2).

The new query has now been automatically called Merge2, but the name can be changed

3. Creating a calculated column

In the final output from Power Query, you may want to perform a calculation based on specific fields within the final output. For example, you could have the number of products sold, and the price for each product. However, there is nothing showing the overall value of what has been sold, which is useful in reporting to senior management. This is where a calculated column can be used.

In the result above, you may wish to add a column that shows the value of the sales in Sales Data (2).

In the Queries and Connections pane (on the right of the screen), right click on Merge2 and select Edit.

A new window will appear showing your query and a new ribbon.

Selectthe Add Column ribbon tab, and then click on Custom Column in the General section of the ribbon.

A screen will appear for you to describe your calculation. The default name for the column is Custom, but you can provide a new name.

Enter the formula into the Custom Column Formula box:

[#”Sales Data (2).Sales”]*[Price Data.Price]

You can do this by double clicking on the Available Columns list.

Click OK and, the new query will be shown with the additional calculated column

Select the Home tab and click on Close and Load. Your edited query will now be displayed on the original sheet:

4. Use Group By to summate a query

Power Query will by default produce a unique row for each row of the query, though this may be too detailed for reporting purposes. If you want to see how many of a particular product has been sold, then a simple query showing every instance of the product would not be very helpful. You can use Group By to summate by product to give the total sold for each product.

On the sample data for Sales Detail, the products cover multiple rows so that there may be several values for a particular product.

You may wish to see the total values by product in your query.

To do this, right click on the Product Details table in the Queries and Connections pane on the right-hand side of the screen. Select Edit and the Editing screen will appear.

Click on the Product column header if this is not already highlighted—this is the column you are going to group by.

Select Group By on the ribbon (under Transform), and a Group By window will appear.

Group By will show as product. Enter ‘Total’ in the new column name box, and change the Operation to Sum. Change the Column name to Sales (this is the column to be summated).

Click OK, and your summated query will be shown. Select Close & Load from the ribbon, and the query will appear on a new sheet, showing totals for each product.

5. Fast Data Load

When importing data from other sources such as databases this can be slow, particularly if there are a large number of records. To speed this up, right click on the data source showing in the Queries and Connections pane on the right-hand side of the screen.

Select Properties and a properties window will appear.

Tick the box for Enable Fast Data Load.

Excel Power Query for smart business management

I hope these business hacks using Power Query in Excel have proven helpful—when used correctly, it can save you time and resources, as well as help unlock powerful insights that lead to better reporting and decision making in management. If you have any questions or comments, let us know with a comment below.

As always, if you’ve got an idea for a new tutorial (we’ve already written about automating cell phone data usage analysis, annual compensation reviews and more), we’d love to hear from you as well!

RELATED POST

How to build an automated report in Excel with Power Query: The time tracking dashboard

Read more

—
Photo by @jonathan_francisca on Unsplash

Facebook

Twitter

Linkedin

Whatsapp

Reddit

Telegram

Richard Shepherd

Richard Shepherd has extensive experience in developing applications in Excel using VBA. He's worked for major financial organizations in the UK including Santander UK, Nationwide Building Society, Zurich Insurance, Royal Bank of Scotland and more. He's written several books on Excel and Access VBA programming, which are published by McGraw-Hill in the U.S. and have been translated into nine languages.