Microsoft Excel 365 Bible

CHAPTER 36Transforming 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
• Working with custom data types

Data 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 www.wiley.com/go/excel365bible.

Once you've downloaded the file, you can import the sample file into Power Query. Open a new workbook, select Data ➪ Get & Transform Data ➪ Get Data ➪ From File ➪ From Text/CSV, browse to and import the LeadList.txt file, and then click the Transform Data 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. The following sections cover 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 ...