Excel 2019 Bible

859

C H A P T E R

39

Transforming Data with Power

Query

IN THIS CHAPTER

Performing common transformations

Creating your own custom columns

Understanding data types

Understanding Power Query formulas

Applying conditional logic

D

ata transformation generally entails certain actions that are meant to “clean” your data—

actions such as establishing a table structure, removing duplicates, cleaning text, removing

blanks, and even adding your own calculations.

In this chapter, you will be introduced to some of the tools and techniques in Power Query that

make it easy for you to clean and massage your data.

You can follow along with the examples in this chapter by downloading the LeadList.txt sample

file at http://www.wiley.com/go/excel2019bible.

Once you’ve downloaded the file, you can import the sample file into Power Query (select Data ➪ Get &

Transform Data ➪ From Text/CSV, browse to the LeadList.txt file, and then click the Edit button).

Performing Common Transformation Tasks

You will find that many of the unpolished data sets that come to you will require various types of

transformation actions. This section covers some of the more common transformation tasks that

you will have to perform.

Removing duplicate records

Duplicate records are absolute analysis killers. The effect that duplicate records have on your analy-

sis can be far-reaching, corrupting almost every metric, summary, and analytical assessment that

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