Excel 2019 Bible

887

C H A P T E R

40

Making Queries Work Together

IN THIS CHAPTER

Reusing query steps

Consolidating data with the Append feature

Understanding join types

Using the Merge feature

D

ata analysis is frequently done in layers, with each layer of analysis using or building on the

previous layer. When you build a pivot table using the results of a Power Query output, you

are layering your analysis. When you build a query based on a table created by an SQL Server

view, you are also creating a layered analysis.

You’ll often find the need to build queries on top of other queries to get the results you’re seeking.

That’s what this chapter is all about. Here you’ll take a look at a few ways that you can advance

your data analysis by making your queries work together.

You can follow along with the examples in this chapter by downloading these sample files from http://www.

wiley.com/go/excel2019bible:

Sales By Employee.xlsx

Appending_Data.xlsx

Merging_Data.xlsx

Reusing Query Steps

It’s common to rely on the same main data table for all kinds of analysis. Even the simple table

shown in Figure 40.1 can be used to create different views: sales by employee, sales by business

segment, sales by region, and so forth.

Of course, you can build separate queries, each performing different grouping and aggregation

steps, but that would mean repeating all of the data cleanup steps you needed before performing

any kind of analysis.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.