Power Query

In a book about nuances and quirks in Excel, I have saved what I consider to be the most nuanced and quirky feature for last: Power Query. I can attest that I picked up and sat down with Power Query multiple times over a series of months before it finally clicked for me. This feature has been around for 10 years, yet most Excel users are unaware of its immense potential.

In this chapter, my goal is to help you use Power Query to automate repetitive processes without writing any programming code. I only have room to share a few examples with you, but along the way, I’ll point out a variety of obstacles and timesavers that will jumpstart your effectiveness. If you find this chapter whets your appetite, Power Query Cookbook, by Andrea Janicijevic, is a comprehensive resource that goes far beyond what I had space to share here.

In this chapter, we will cover the following topics:

• Introducing Power Query
• Creating a list of worksheets
• Automatic report...

Technical requirements

You will have the best experience in Power Query if you’re using Microsoft 365 or Excel 2021, but most of what I will share can be conducted in Excel 2010 and later. At the time of writing, Power Query is in beta testing for Microsoft 365 users of Excel for macOS. Anyone using Excel 2010 or Excel 2013 will need to download and install the free Power Query add-in. You can access Power Query in Excel 2016 by way of Data | New Query instead of Data | Get Data, which you must do in Excel 2019 and later. Microsoft 365 or Excel 2021 is required to extract data from PDF files. The workbooks, PDF file, and text files that I used in this chapter can be downloaded from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-Treasures/tree/main/Chapter12.

Introducing Power Query

Each time you use Power Query, you will work through a series of at least three, and sometimes as many as five, phases:

• Connecting: Power Query enables you to connect to a variety of data sources, including worksheet ranges, workbooks, text files, PDF files, and databases.

Nuance

Any worksheet ranges that you connect to within an Excel workbook will automatically be converted into Tables.

• Transforming: The Power Query Editor enables you to shape your data, much like a potter shapes a lump of clay, but in this case, you can opt to remove unnecessary rows and columns, add columns, separate data from a single column into two or more columns, and much more.
• Combining: The optional combining phase enables you to stack different datasets together, such as combining individual worksheets from a workbook into a single list or merging data, which is akin to using lookup formulas in Excel to match related data from a second list into list...

Creating a list of worksheets

I can visualize situations where an auto-generated list of worksheets would be a helpful addition to many workbooks. A couple of ideas include tracking the progress of a workbook audit, eyeballing a simple list of all worksheets in a workbook, or maybe taking things up a level by building a clickable worksheet index. Such an index may feel redundant. After all, when right-click on the navigation arrows in the bottom left-hand corner of Excel you can navigate by double-clicking on any worksheet Name the Activate dialog box. On the other hand, this index will be a self-updating listing that includes the Names of hidden worksheets and can be sorted alphabetically if desired. Let’s jump in::

1. Open the Chapter 12 – Workbook Index.xlsx example workbook for this chapter, which contains 17 worksheets.
2. Activate the Summary tab so that your index will appear as the first sheet in the workbook.

Tip

When you load data from Power...

Automatic report cleanup

You may find yourself saddled with reports that you need to analyze in Excel that are particularly unfriendly from an analytical standpoint. You may even be manually cleaning up such reports monthly. Or perhaps you’re writing macros in Excel to automate repetitive tasks such at this. I will tell you that I write far fewer macros these days now that Power Query is available.

As we saw in the workbook index example, Power Query offers a code-free approach that is designed to be “set-and-forget.” We're going to transform a January accounting report into an analysis ready format, and then save a February report over it. This will illustrate how once you establish Power Query, the only repetitive step is to run the new report and save over the previous version of the data source.

First, let’s look at some common frustrations that arise in reports that you export to Excel from other software programs.

Analytical obstacles...

Extracting data from PDF files

Some users purchase third-party software to extract data from PDF files due to a lack of better alternatives. PDF stands for Portable Document Format, a product of Adobe Corporation. PDF files are cross-platform compatible, and can be opened on any device. Extracting data from PDF files with Power Query requires Excel 2021 or Microsoft 365. The steps in this section cannot be carried out in Excel 2019 or earlier. At the time of writing, Excel for macOS does not support extracting data from PDF files.

Tip

Word 2013 and later allows you to open and edit most PDF files if your version of Excel doesn’t allow extracting data with Power Query. Open a PDF file in Word the same way you would open a Word document, and then copy and paste the results over to Excel. Some PDF documents will transfer cleanly, while others will be a jumbled mess.

Any software that extracts data from PDF files uses optical character recognition, so somtimes your data...

Unpivoting data

Unpivoting data is sometimes referred to as flattening the data, which means that all similar values appear in columns instead of rows. For instance, the Profit & Loss By Class report that I exported from QuickBooks Desktop, shown in Figure 12.24, spans 123 columns, running from column A to column DS. It’s hard to do much with a report like that since the data is so broadly dispersed. Let’s use Power Query in Excel for Windows or macOS to transpose 123 columns of data into three. Once again, I’ll move faster by not renaming anything in the Applied Steps list:

Figure 12.24 – Data to be unpivoted

1. Press Ctrl + N ( + N) to create a blank workbook (or choose File | New and choose Blank Workbook).
2. Click Data | Get Data | From File | From Excel Workbook, select Chapter 12 – Unpivoting Data.xlsx, and then click Import.
3. Select Sheet1 from the list and then click Transform Data.
4. Click Column1 and...

Appending and merging data from multiple sources

At this point, we’ve seen how to extract data from a single data source, such as a PDF file or Excel workbook. In this section, I’m offering you a two-for-one, where you’ll not only learn how to extract data from text files but also how to relate data from two or more sources together. The example files for this chapter include six comma-separated value (CSV) files. Figure 12.26 shows the data contained within each file, which is also in the Chapter 12 - Appending and Merging Data.xlsx workbook. These text files are structured similar to how data is stored in database Tables.

You may not have immediate access to a database, but carrying out the steps for appending and merging data from these text files, then you’ll know exactly extract data from a database into Excel:

Figure 12.26 – Normalized data

Normalizing data means having as little redundancy as possible. In this example...

Connecting to databases and installing ODBC drivers

You’ll notice that this book doesn’t have anywhere near 1,000 pages, which means there’s simply not enough space to dive very deep into the topic of connecting to databases and installing ODBC drivers. With that said, the examples in this chapter were chosen to give you exposure to using Power Query with a variety of data sources. As you’ll see, connecting to a database typically only involves a couple of steps, depending on the platform that you’re connecting to.

Establishing an Access database and SQL Server connections

Let’s start by linking to an Access database in Excel for Windows (you cannot connect to Access databases in Excel for macOS):

1. Press Ctrl + N to create a blank workbook (or choose File | New and choose Blank Workbook).
2. Choose Data | Get Data | From Database | From Microsoft Access Database, select the Chapter 12 – Fruit Sales.accdb database from...

Summary

In this book about nuances and quirks in Microsoft Excel, I saved the most nuanced and quirky feature for last. Power Query can be a transformational tool for you if you can avoid getting stymied. I’ve shared several stumbling blocks that I hit during my learning curve. You should now be well prepared for most things that Power Query may try to throw at you.

In this chapter, you learned how to create a list of worksheets within any workbook, which you can then transform into an interactive index listing. It’s important to understand that Power Query does not offer real-time connections to data, but rather provides snapshots of the data that can be refreshed. Then, we transformed an Excel-based report from an accounting program into a streamlined, analysis-ready dataset.

If you’ve struggled to extract data from PDF files in the past, the ability to do so with Power Query may have you dancing in your chair. I know that being able to unpivot reports...