The Engine Behind Big Data: Power Query

Excel is one of the most widely used software tools today. You don’t have to be an accountant anymore to use Excel due to the simplicity of its interface and the power and versatility that it has. But what if I told you that you’re only using Excel at 40% capacity? 

Some of Excel’s best kept secrets don’t actually come ready to use in Excel. If you are looking to take your Excel game to the next level, get ready for a high-level overview of Power Query.

What is Power Query?

Power Query is an add-in that helps with data automation, created and maintained by Microsoft and comes with almost all versions of Excel (you just need to enable it). It can pull, transform, and output data from various sources to Excel.

Why is it important?

Every company is different, but where I have found Power Query to be most useful is to automate repetitive tasks from data sources with consistent formats. I’ll explore a few real-world examples below:

1)     Monthly Financial Package. A client of ours requested a monthly financial package in Excel, a standard request for many companies. All of the client’s inputs, such as their GL accounts, remained quite similar month over month. We imported their general ledger data from their Enterprise Resource Planning (ERP) system and ran it through multiple custom Power Queries designed to export data to different Excel workbooks. It not only cut the financial statement prep time from 15-20 hours a month to 1-2 hours a month, it also helped us catch errors or changes in the GL data (such as new GL accounts) when things weren’t working properly.

2)     Parsing Data. We often work with companies that have a ton of data, but the analysis that we are performing only takes a smaller subset of that data. Instead of including all 100,000 rows into the analysis workbook, you could have a workbook that houses all the original, raw data, and use Power Query to automatically create a subset of that data for the needs of a specific workbook. This reduces the time to complete or update that workbook while ensuring that the data integrity stays intact by linking directly to the data source. A bonus here is that it will auto-update when new data is added, all you need to do is refresh the connection!

How to Excel with Power Query

Installation: This Excel Campus article shows you how to install Power Query to your Microsoft Excel instance.

Get Data: Once you have Power Query installed, under the Data tab, you can see there is a “Get Data” button.

From here, you can connect to all sorts of data sources including, but not limited to, Excel, Text/CSV, Google Sheets, PDF, website, and much more.

Transform Data: Once you upload the data from your data source, you can then transform the data. There are a ton of options here from appending and merging queries, removing rows and columns, replacing values and much more. Below, you will find three of the most important fields. 

1. Queries – This shows all the databases to which Power Query is currently connected.

2. Applied Steps – This shows all the data transformation that has happened from the original source data.

3. Formula Bar – This provides additional detail into what the Applied Step accomplished, in the example’s case, it removed the column “Edition.”

Outputting Your Data

Once you’ve transformed your data, you’re ready to output to Excel. Find the Close and Load button, like below and Excel will automatically open a new tab (or update a tab that already has this data in it).

This was just a brief introduction to how powerful Power Query can be. Every Excel user should have this in their toolkit. I plan to do a deeper dive in the coming months, but if you have any questions on how to install, use cases, or anything else, let us know in the comments section below.