Level 2 · Fix types and messy text in a customer export

A report can only be as clean as its data. Brittany wants to count customers per city, and with Tampa, tampa and tampa in the same column, a visual would show three Tampas. This level is the clean-up pass you’ll run on almost every export.

The practice editor

As in the rest of this chapter, you work in a practice editor built for this site: Power Query’s step names and results, one step per line, the table after each step in the preview. In Power BI Desktop the same steps live in Home → Transform data, recorded under Applied Steps in the Query Settings pane.

Look before you clean

Power Query helps you find the problems. On the View tab, tick Column quality (valid, error and empty percentages under each header), Column distribution (distinct and unique values) and Column profile (value counts for the selected column). By default they profile the first 1,000 rows; click the text in the status bar to switch to the entire data set. Here, Column profile on city lists Tampa, tampa and tampa as separate values: the first sign of trouble.

Types first

The export wrote every column as text. The icon left of each header shows the type: ABC for text, 123 for whole number, a calendar for date. Text IDs sort as 1001, 11, 2, and text dates can’t be filtered by month.

Change type: customer_id to whole number
Change type: signup_date to date

In Power BI: click the type icon on the header, or Transform → Data Type. Power Query writes a Changed Type step:

Table.TransformColumnTypes(Source, {{"customer_id", Int64.Type}, {"signup_date", type date}})

Clean the text

Trim: city, state
Capitalize each word: city
Uppercase: state

All three are under Transform → Format (select the columns first; Ctrl+click selects several). Trim removes spaces at the start and end, Clean (in the same menu) removes invisible control characters, Capitalize Each Word writes Tampa and St. Petersburg, and UPPERCASE writes FL. The M for the first one:

Table.TransformColumns(#"Changed Type", {{"city", Text.Trim, type text}, {"state", Text.Trim, type text}})

Missing is not a value

N/A looks like a referral source called “N/A”. In a chart it would get its own bar, and a count of known sources would include it. Replace it with a real missing value:

Replace values: referral_source "N/A" with null

In Power BI: select the column, Transform → Replace Values, Value To Find N/A, Replace With null. Power Query compares the whole cell, case-sensitively, so n/a would need its own step.

Order of steps

Steps run top to bottom, each on the previous step’s result. Trim before you compare or deduplicate on a text column; change types before you filter by date. If a step was added in the wrong place, drag it up or down in Applied Steps, or right-click → Delete Until End to start again from there.

Common mistakes

• Fixing the visual instead of the data. Renaming one bar doesn’t fix the next refresh.
• Uppercasing a city. TAMPA is consistent but hard to read; proper case is the convention.
• Typing values over cells. Power Query has no cell editing; every fix is a recorded step, and that’s what makes it repeat on refresh.