Excel Cookbook

Chapter 15. Power Query

Power Query is a powerful data transformation tool that lets you import external data, perform a set of data manipulations, and then load the results into Excel. If the underlying data changes, you can update it in Excel by refreshing it. Power Query has four main phases:

1. In the connect phase, you specify which data you want to import and how to access it.

2. Once you’ve connected to the data, you can transform it using the tools in the Power Query Editor. For example, you can apply filters, split or merge columns, and invoke custom functions.

3. You can optionally combine datasets by appending or merging their data.

4. Finally, you load the data into Excel.

This chapter includes recipes for each phase.

Note

Power Query, or Get & Transform, is available in Excel 2016 or later for Windows. Many features are available in Excel 365 for Mac, Excel for Web supports some features, and other Microsoft products—such as Power BI—include it too. This chapter focuses on those features available in Excel 365 for Windows.

15.1 Getting and Loading Data

Problem

You want to get data from a file, the web, a database, or another source and load it into Excel.

Solution

Power Query lets you get data from various sources, including files, databases, and websites (depending on your version of Excel). Once you’ve specified which data to import, you can shape and transform it using the Power Query Editor and then load the results into Excel.

To get the data, choose Data ...