Profiling and Cleaning Data

Introduction

This lesson continues the domain Prepare the data (25–30% of the PL-300 skills measured, outline dated April 20, 2026). Before you model or visualise anything, you must know what is in the data and fix what is wrong with it. Bad data produces confident but wrong reports, and the exam tests whether you can find the problem with Power Query's profiling tools and choose the right repair. Microsoft does not publish the question count or the mix of question types; OnLevelUp mock exams use a 50-question practice length and an 80% practice target, neither of which is official or a conversion of the 700/1000 passing score.

After this lesson you will be able to profile columns with the data preview options, evaluate quality and distribution, resolve inconsistencies and nulls, and diagnose and fix import errors.

Learning objectives

• Turn on and interpret column quality, column distribution and column profile.
• Explain how profiling scope (first 1,000 rows versus entire data set) can mislead.
• Resolve inconsistent values, duplicates, nulls and invalid values with the appropriate Power Query transformation.
• Diagnose and resolve import errors such as type conversion, missing columns and refresh failures.

1. Profiling the data

Profiling happens in the Power Query Editor (Home → Transform data). On the View tab, in the Data preview group, three options help you inspect columns:

Definitions the exam likes: distinct values counts each different value once (A, A, B has 2 distinct), whereas unique values counts values that appear exactly once (A, A, B has 1 unique). A column intended as a key should have distinct = unique = number of rows.

By default, profiling is based on the first 1,000 rows. The status bar at the bottom of the editor shows "Column profiling based on top 1000 rows". Click it and choose Column profiling based on entire data set to analyse everything. The consequence: an error in row 50,000 does not show in the default view, yet it will break a full refresh.

Example. Contoso Health loads a patient visits table with 3 million rows. The column quality for VisitDate shows 100% valid in the top 1,000 rows. After switching to the entire data set, 2.3% of values show errors, caused by dates stored as 31/02/2025 in a legacy import. Only the full-profile view reveals the problem before a scheduled refresh fails.

Two other helpers: the Query Dependencies view (View → Query dependencies) shows how queries relate, and the Query Diagnostics tools help find slow steps. For limiting the preview cost during development you can keep the top-1,000-row profile and validate on the full data set before publishing.

2. Evaluating quality and distribution

Look for these signals:

• Errors (red) in column quality: conversion failures, division by zero, missing fields in an expanded record.
• Empty (dark grey): nulls or empty strings.
• Skewed distribution: one value dominating (for example 98% "N/A") suggests a placeholder.
• Unexpected distinct count: Country with 240 distinct values when 60 are expected indicates spelling variants such as "USA", "U.S.A." and "United States".
• Min/max outside the expected range: ages of -1 or 999 are sentinel values.
• Leading and trailing spaces, mixed case, non-printing characters: invisible but break joins and relationships.

Exam tip: the question often asks for the first step. Profiling (column quality/distribution/profile) comes before cleaning, and full-data-set profiling comes before trusting a quality percentage.

3. Resolving inconsistencies, nulls and quality issues

Text inconsistencies

Under Transform → Format: Trim removes leading and trailing spaces, Clean removes non-printing characters, and lowercase / UPPERCASE / Capitalize Each Word standardise case. Use Replace Values (right-click a column) to map variants ("U.S.A.", "USA") to one value. For many variants, create a small mapping table and merge it (see the next lesson).

Merges and relationships are case-sensitive and space-sensitive in Power Query, so "Paris " and "paris" will not match "Paris". Fixing the text before merging avoids silent mismatches.

Duplicates

Remove Duplicates (Home → Remove rows) keeps the first occurrence based on the selected columns. Select only the key column(s) that define identity; selecting all columns removes only rows identical in every column. Because comparison is case-sensitive, standardise case first. Also remember that removing duplicates in a column drops entire rows.

Nulls and empty values

Empty strings ("") differ from null. A text column may need Replace Values for both, or a custom column using if [Col] = null or [Col] = "" then "Unknown" else [Col].

Invalid values and errors

• Replace Errors converts error cells to a chosen value; use it deliberately because it hides the cause.
• Remove Errors drops the whole row, which can silently remove valid data from other columns.
• Keep Errors isolates problem rows so you can see them and fix the source.
• Use Conditional Column (Add Column → Conditional Column) for rules such as "if Age < 0 or Age > 120 then null".

Worked example 1: cleaning a country column. Scenario. A Customers table has a Country column with "France", "france ", "FR", "United States", "USA", "U.S." and blanks. Column distribution shows 41 distinct values for about 20 real countries. The analyst must produce a clean column that can relate to a Country dimension.

Reasoning.

1. Trim and Capitalize Each Word on Country to remove spaces and case variance ("france " becomes "France").
2. Replace Values for known codes: FR → France; USA and U.S. → United States.
3. Replace null and "" with Unknown so that those rows are not lost in the relationship.
4. Re-check column distribution: distinct count should now be about 21 (20 countries plus Unknown).
5. If many variants remain, load a mapping table and merge on the raw value to return the standard name, which is easier to maintain than dozens of Replace steps.

Why alternatives are worse. Removing rows with unmatched countries loses sales. Fixing the values in DAX with a calculated column duplicates cleaning in the model and increases model size. Editing the source file by hand does not survive the next export.

4. Resolving data import errors

Errors fall into several families.

Type conversion with locale

A date such as 03/04/2025 means 3 April in the UK and 4 March in the US. Use Change Type → Using Locale and choose the locale of the source (for example English (United Kingdom)). Applying the wrong locale produces swapped days and months without any error, so it is a data-quality trap rather than a visible failure.

try ... otherwise

M offers try expression otherwise fallback to handle errors per value:

= Table.AddColumn(Previous, "Amount", each try Number.From([RawAmount]) otherwise null, type nullable number)

This converts bad values to null instead of an error; follow it with a count of nulls so you can report how much data was lost.

Reading errors in the interface

Click the word Error in a cell to see the message in the preview pane. Use Keep Errors (Home → Keep rows) to list only error rows. For a refresh error in the service, the Refresh history of the semantic model shows the failed step and message; fix the root cause in Desktop or in the data source settings.

Worked example 2: a refresh that suddenly fails. Scenario. A monthly refresh of a model fed by an Excel file fails with "The column 'Revenue' of the table wasn't found". The finance team renamed the column to Total Revenue in the file. How should the analyst fix it, and how can they reduce repetition of the problem?

Reasoning.

1. Open the failing query in Power Query. The step Changed Type or Renamed Columns references Revenue.
2. Rename the source reference to Total Revenue, or add a step that renames Total Revenue to Revenue so the rest of the model stays unchanged.
3. Confirm that the downstream steps and measures still use Revenue.
4. To prevent recurrence, agree a template with finance, or use Table.RenameColumns(Source, {{"Total Revenue", "Revenue"}}, MissingField.Ignore) so the query works with either name.
5. Republish and refresh.

Why alternatives are worse. Deleting the step breaks the data type. Re-importing a new query creates a second table and breaks relationships and measures. Waiting for finance to revert is not a robust process.

5. Comparison: ways to handle bad values

6. Pitfalls and exam tips

• Do not trust the default top-1,000-row profile for a large table.
• Replace blanks and nulls deliberately; a numeric column with nulls averages differently from one with zeros.
• Clean text before merging, removing duplicates or creating relationships.
• Avoid Remove Errors when other columns in the row are valid.
• A wrong locale does not raise an error; check sample dates after conversion.
• Exam tip: when two answers fix the problem, prefer the one applied earliest in the pipeline (source or Power Query) over a DAX workaround, because cleaning once is cheaper than repairing every measure.

7. Mini case study

Case. Tailwind Airlines imports a Bookings CSV. Column quality shows 4% errors in FareAmount (values such as "USD 120.50"), 12% empty PassengerEmail, duplicates of BookingID (distinct 98,000 vs 100,000 rows) and Departure dates shown as 05/06/2025 where the airline operates in the UK. The file is profiled on the top 1,000 rows.

Tasks. Describe what to do, in order.

Solution.

1. Switch profiling to the entire data set to confirm the counts.
2. FareAmount: use Replace Values to remove the "USD " prefix (or split by a delimiter), then change type to Decimal Number. Keep the currency in a separate column if needed. Check remaining errors with Keep Errors.
3. PassengerEmail: 12% empty is acceptable if email is optional; replace nulls with "Unknown" only if the column is used in visuals. Do not drop rows.
4. BookingID: it should be unique. Investigate with Group By (count rows per ID) to see if duplicates are exact copies or different bookings. If exact copies, use Remove Duplicates on BookingID; otherwise raise it with the data owner.
5. Departure: Change Type → Using Locale with English (United Kingdom) so 05/06/2025 is 5 June.
6. Validate by profiling again: errors 0%, distinct = unique for BookingID.

8. Extra practice: profiling a sales feed end to end

Scenario. Litware Outdoor receives a daily OrderLines export with 1.8 million rows. The BI developer must decide whether it is safe to load. A repeatable routine saves time on the exam and in real projects:

1. Shape first. Check the row count, column count and header row. If titles sit above the header, remove top rows and promote headers.
2. Keys. Turn on column distribution for OrderLineID. If distinct is lower than the row count, duplicates exist; if unique is lower than distinct, some IDs repeat. Decide whether rows are true duplicates (remove) or legitimate split shipments (keep, and use a composite key).
3. Measures. Open column profile for Quantity and UnitPrice. A minimum of -5 may be returns, which is valid, whereas a maximum of 99999 repeated hundreds of times is a sentinel value that should become null.
4. Dates. Verify the range: a minimum of 1900-01-01 usually reveals a default date from the source system. Replace it with null so it does not stretch the date axis.
5. Categories. Look at distinct counts for ProductCategory and Region. Unexpected extras mean spelling variants; compare to the dimension table.
6. Text hygiene. Apply Trim and Clean to every text column that will be used as a key or in a relationship.
7. Document. Name the steps clearly (for example "Replace 1900 dates with null") so a reviewer understands the intent. The Applied Steps list is part of the solution quality.

Removing columns that contain nothing useful is also a performance technique: smaller models refresh faster and use less memory, which comes back in the lesson on model optimisation.

Key terms

• Column quality / distribution / profile: profiling views in Power Query.
• Distinct vs unique: different values counted once vs values appearing exactly once.
• Null vs empty string: missing value vs zero-length text.
• Locale: culture used to parse dates and numbers.
• Fill Down: copies the previous non-null value into nulls.

Summary checklist

• I can enable the three profiling options and switch to entire-data-set profiling.
• I can tell distinct from unique and use them to validate keys.
• I can trim, clean, standardise case and replace values before merging.
• I can choose between Remove, Replace and Keep Errors with their consequences.
• I can fix type conversion, locale and missing-column refresh errors.

Official sources

• Study guide for Exam PL-300: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/pl-300 (opens in a new tab)
• Data profiling tools in Power Query: https://learn.microsoft.com/en-us/power-query/data-profiling-tools (opens in a new tab)
• Power Query error handling: https://learn.microsoft.com/en-us/power-query/dealing-with-errors (opens in a new tab)