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

Excel Power Query: The 5 Hacks for Business Intelligence You Should Have in Your Arsenal

Table of Contents

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)

Business Intelligence with Excel Power Query

Sales Data (2)

Power Query and Excel BI: a tutorial

Price Data

5 Hacks for Business Intelligence with Excel Power Query

Sales Detail

What is Excel Power Query

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:

Excel Power Query for Business Management

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.

5 things managers can do with Excel Power Query

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:

What can managers do with Excel Power Query?

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:

5 hacks for Excel BI

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:

Excel for Business Intelligence

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.

5 Hacks of Excel for Business Intelligence

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:

Business Intelligence with Excel BI and Power Query

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).

A step by step tutorial on how to use Excel Power Query for Business Intelligence

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.

Excel BI Power Query

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.

What is Excel Power Query for Business Intelligence?

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!


Photo by @jonathan_francisca on Unsplash

The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company/tool or their past/present organizations, employers, or associates.
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.

Comments

Related posts

How to use Google Sheets’ pivot tables, column stats, explore, & QUERY function

Published: 2021/9/15 | David Benaim

As a manager, perhaps you’ve wondered how to take your financial reporting to the next level or how to amp up your data analysis skills. Maybe you’ve got an important meeting coming up, or you’d simply like to wow the executives on your next run-through. Whatever the reason, this tutorial is sure to help. We’re […]

Read more

Mastering budget vs. actuals analysis: Excel Power Query tutorial + FREE template

Published: 2021/11/10 | Tony De Jonker

In this tutorial, learn how to create a budget vs. actuals report in Excel using Power Query. Gain insights and track financial performance effortlessly. As a financial controller, accountant, or CFO, you’re likely familiar with the concept of budget vs. actuals. You know that reporting budget vs. actuals can be both cumbersome and time-consuming, given […]

Read more

How to calculate project profitability using time tracking data: Everything you need to know [Excel tutorial]

Published: 2022/7/19 | Andrew Childress

If you bill clients based on the time you spend, here’s a pop quiz: which projects make the most money? Or even more importantly: which projects aren’t profitable? If you don’t know the answer to these questions, it’s time to start thinking about project profitability, a measurement of revenue billed versus time and cost expended. […]

Read more