Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 56 ยท Power Query

Cleaning Data: Types, Nulls, Trim and Duplicates

Fix the most common data problems in Power Query: data types, errors, nulls, stray spaces, duplicates and header rows, with M code and pandas equivalents.

Dirty data causes quiet damage. A date stored as text will not sort correctly, "Berlin " with a trailing space will not match "Berlin", and duplicate rows double your revenue. The cleaning steps in Power Query are small, but doing them before the data reaches your model saves you from chasing odd numbers in a report later.

This lesson covers the cleaning toolbox in the order you usually need it: headers, data types, errors and nulls, text hygiene and duplicates. Each step shows the M code Power Query writes (read-only) and a pandas version you can run. The editor itself is introduced in The Power Query Editor.

A usual cleaning order

Headers Promote the first row.
Types Text, number, date.
Text Trim and clean.
Nulls and errors Replace or remove.
1 / 2

Promote headers

Files such as CSVs or Excel sheets often arrive with column names in the first data row. Choose Use First Row as Headers on the Home ribbon (or the table icon in the preview). If the headers are in a lower row, first remove the top rows (Remove Rows > Remove Top Rows), then promote.

M
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true])

The reverse operation, Use Headers as First Row, exists too, which is handy when headers were promoted by mistake.

Data types

The data type controls how a column behaves: sorting, filtering, aggregation and even which functions are available. Click the icon in a column header to pick a type. The main ones:

Icon type M type Use for
Whole Number Int64.Type counts, IDs, quantities
Decimal Number type number prices, measurements
Fixed Decimal Currency.Type money
Date, Date/Time type date, type datetime dates
Text type text names, codes
True/False type logical flags
M
#"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {{"OrderDate", type date}, {"Quantity", Int64.Type}, {"UnitPrice", type number}}
)

Two points deserve care:

  • Locale. The text 03/04/2025 is March 4 in the US and April 3 in much of Europe. If the default conversion guesses wrong, use Change Type > Using Locale and pick the locale of the source. Always check a date column after converting.
  • Codes that look like numbers. ZIP codes, phone numbers and product codes with leading zeros (00123) must stay text, or the zeros disappear.

When a value cannot be converted (for example the text "n/a" in a number column) the cell becomes an Error instead of silently disappearing. That is useful: you see the problem.

Data types and conversions in depth

Choosing the right data type is one of the most important steps of any model, because it affects accuracy, file size, sorting and which calculations are allowed. Here is the full list as Power Query shows it, with what happens when the data reaches the model.

Type Holds Notes
Whole number 64-bit integer (about ยฑ9.2 quintillion) Counts, IDs, quantities. Exact.
Decimal number 64-bit floating point, about 15 significant digits Measurements. Tiny binary rounding errors are possible, as in 0.1 + 0.2.
Fixed decimal number exactly 4 decimal places, up to 19 digits in total Money. Exact for currency calculations. Called Currency in DAX.
Percentage a number shown as a percent Same storage as a decimal; only the display differs.
Date/Time date and time of day In the model, Date and Time are stored as Date/Time too.
Date a date without a time Use for a date table's key.
Time a time of day without a date Rarely useful on its own.
Date/Time/Timezone a date and time with a UTC offset Converted to a plain Date/Time (in UTC) when loaded to the model.
Duration a length of time (days, hours, minutes...) Becomes a decimal number of days in the model.
Text Unicode text Codes with leading zeros, names, descriptions.
True/False Boolean Flags.
Binary the raw content of a file Used while combining files; not loaded as a column in a report.
Any not yet decided A column of type Any is a warning sign: set a real type.

Detecting and changing types

For file sources such as CSV and Excel, Power Query can detect column types automatically and by default looks only at the first 200 rows of the data. A column that is whole numbers in the first 200 rows and has a decimal in row 5,000 is then typed as whole number, and the later value causes an error. Check the setting under File > Options > Power Query Editor > Type detection, and prefer setting types yourself on the final columns. On the Transform tab, Detect Data Type runs detection on a selected column, and Data Type (or the icon in the header) sets it.

Always change types after cleaning text: a column with "$1,250" must lose the symbol and the comma before it can become a number. With errors still possible, the safe M pattern is:

M
#"Cleaned" = Table.TransformColumns(
    #"Trimmed",
    {{"Amount", each try Number.FromText(Text.Remove(_, {"$", ","})) otherwise null, type number}}
),
#"Typed Dates" = Table.TransformColumnTypes(#"Cleaned", {{"OrderDate", type date}}, "en-GB")

The third argument of Table.TransformColumnTypes is the culture used to interpret the text, which is how 03/04/2025 is read as 3 April (en-GB) or 4 March (en-US). Date.FromText("2025-04-03", [Format = "yyyy-MM-dd"]) is the most explicit way to convert one value. For time zones, DateTimeZone.SwitchZone(value, 2) re-expresses a value at UTC+2 and DateTimeZone.ToUtc converts it to UTC. A duration is created by subtracting two dates, and Duration.TotalDays (or Duration.Days) reads it.

Converting with DAX

When the type must change in the model instead of in Power Query, DAX has conversion functions:

DAX
Amount Number = VALUE ( Sales[AmountText] )
Whole         = INT ( Sales[Price] )
As Text       = FORMAT ( Sales[Price], "#,0.00" )
Date From Text = DATEVALUE ( Sales[DateText] )
As Integer    = CONVERT ( Sales[Quantity], INTEGER )
As Currency   = CURRENCY ( Sales[Price] )

DAX also converts implicitly: "5" + 3 is 8 (text becomes a number because + needs numbers), while "5" & 3 is "53" (the number becomes text because & joins text). Implicit conversion is convenient and the source of confusing bugs, so write the conversion you mean. Changing a type in Power Query is almost always better than converting in DAX, because it happens once at refresh, is stored compactly and can fold to the source.

Fixing mixed-type columns

A column holding numbers in some rows and text such as n/a, - or 12 units in others cannot have one type. The approach:

  1. Look at the Column quality and Column distribution profile options (View tab) to see how many values are errors or empty.
  2. Clean the text: trim, remove symbols and units, replace placeholders such as n/a by null.
  3. Change the type to number, and replace the remaining errors with null (or investigate them).
  4. Keep a record of what you changed, because quietly turning n/a into blank changes the meaning of averages.

Try it: types in Python

The stand-in shows three of the issues above: float versus exact decimals, a time zone and a duration, and a mixed-type column.

Python Stand-in: decimal vs fixed decimal, time zones, durations and mixed types

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The n/a and - rows become blanks, so the average is taken over four values, not six. That is the cost of cleaning: whichever way you handle them, say so in a note. The order placed at 23:30 in UTC-5 is already the next day in UTC, which is why you should keep a date table in one agreed time zone.

Numeric formatting is not rounding

On Column tools you can set a column's Format (whole number, decimal, currency, percentage), the number of decimal places, the thousands separator and its default summarization (Sum, Average, Don't summarize). These settings change how a value is displayed, not what is stored: a price of 12.3456 shown with two decimals still contributes 12.3456 to a sum. Set the default summarization to Don't summarize for IDs, years and codes so they are never added up by accident, and use a measure when you need a rounded stored result, as in DAX Math and Statistical Functions.

Errors and nulls

An error is a failed calculation or conversion. A null is an empty value. They need different treatment.

  • Remove Errors (Home > Remove Rows) deletes rows that contain an error in the selected columns. Replace Errors swaps the error for a value such as null or 0.
  • Replace Values swaps one value for another. Replacing with nothing leaves a null.
  • Fill Down / Fill Up copies the nearest value into null cells. It is the standard fix for exports where a group label appears only on the first row of each group.
  • Remove Empty (in the column filter menu) drops rows with null or blank values.
M
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Quantity", 0}}),
#"Replaced Value" = Table.ReplaceValue(#"Replaced Errors", "n/a", null, Replacer.ReplaceValue, {"Region"}),
#"Filled Down" = Table.FillDown(#"Replaced Value", {"Region"})

Decide consciously whether a missing quantity should be 0 or stay null: sums treat null as "nothing" while averages skip it, and 0 pulls an average down.

Trim and clean text

Hidden characters cause the nastiest bugs, because the values look identical. Under Transform > Format:

  • Trim removes leading and trailing spaces.
  • Clean removes non-printing characters such as line breaks and tabs.
  • Lowercase / UPPERCASE / Capitalize Each Word standardize case.
M
#"Trimmed Text" = Table.TransformColumns(#"Filled Down", {{"City", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text", {{"City", Text.Clean, type text}}),
#"Capitalized" = Table.TransformColumns(#"Cleaned Text", {{"City", Text.Proper, type text}})

Note that M text comparisons are case-sensitive: "berlin" and "Berlin" are different values until you standardize case. This will matter again for joins in Merge and Append Queries.

Remove duplicates

Select one or more columns, then Remove Rows > Remove Duplicates. Only the selected columns are compared, and the first row of each group is kept.

M
#"Removed Duplicates" = Table.Distinct(#"Capitalized", {"OrderID"})

Two warnings. First, duplicates are found with the same case-sensitive comparison, which is another reason to trim and fix case first. Second, if you select all columns you remove only rows that are identical everywhere; selecting just the key column removes rows that merely share a key, even if other columns differ. Decide which meaning you want.

Removing duplicates on a key column silently keeps whichever row came first. If rows with the same key differ (say, two prices), sort the table first so the row you want to keep is on top, or investigate why they differ before deleting anything.

A pandas version of the cleaning steps

The data below has the usual problems: a note in the text of a number column, padded and oddly cased cities, a null in a group column and a duplicated order. Each pandas line is labeled with the Power Query step it matches.

Python Cleaning with pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice how "n/a" became 0 only because we chose that, and that the empty Region of order 1002 was filled with the value above it. The duplicated row for order 1001 is gone. Because the trimming happened before drop_duplicates, the two copies matched.

Why order matters: the invisible space

This second example proves why trimming comes before duplicate removal.

Python Trim before you deduplicate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Four values counted as four distinct cities (including Berlin three times over) until we standardized them, after which only two remain. In Power Query the same thing happens if you remove duplicates before trimming.

Keep in mind what is real here and what is not. The M snippets are what Power Query generates; pandas is only a stand-in, and some defaults differ (for example pandas leaves bad numbers as NaN, while Power Query marks them as errors you can see and handle explicitly).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A ZIP code column contains values like 02134. Which type should it have?

    Whole Number would drop the leading zero (2134). Identifiers and codes should be text, because you never do arithmetic on them.
  2. 2. Which step is the right fix for an export where a group name appears only on the first row of each group?

    Fill Down copies the nearest non-null value from above into the empty cells below it.
  3. 3. Why should you trim text before you remove duplicates?

    Comparisons are exact. Berlin and Berlin with a trailing space are different values until Trim makes them identical.
  4. 4. What does Remove Duplicates do when you select only the OrderID column?

    Only the selected columns are compared. The first row of each group is kept, so differences in other columns are lost.

Summary

  • Promote headers first, then set data types (check dates and codes carefully).
  • Errors mark failed conversions; nulls are empty. Replace, fill or remove them on purpose.
  • Trim, Clean and case changes make equal-looking text actually equal.
  • Remove Duplicates compares only the selected columns and keeps the first row.
  • Clean in an order that makes values consistent before deduplicating or joining.

Next up is reshaping the table itself in Shaping Columns and Rows.