Clean, Convert and Filter Data

What it is

Raw data is rarely clean. The same customer appears twice, a region is empty, a date arrives as text in a regional format, and test orders sit next to real ones.

Cleaning fixes these problems before the data reaches a star schema or a semantic model. The DP-600 exam expects you to recognise each problem and pick the right fix in T-SQL, PySpark or Power Query (Dataflow Gen2).

How it works

Profile the data first

Power Query in Dataflow Gen2 and Power BI Desktop shows data profiling on the View tab:

By default the profile uses only the top 1,000 rows. Switch it to the entire data set from the status bar before you trust it.

Duplicates

When you must keep a specific row, such as the latest version, rank the rows:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerID
               ORDER BY ModifiedDate DESC) AS rn
    FROM stg.Customer
)
SELECT CustomerID, CustomerName, Region, ModifiedDate
FROM ranked
WHERE rn = 1;

dropDuplicates on a key keeps one row per key, but you do not control which one. Use a window function when the choice matters.

Missing data and nulls

SELECT OrderID,
       COALESCE(Region, 'Unknown') AS Region,
       ISNULL(Discount, 0) AS Discount
FROM stg.Orders
WHERE OrderID IS NOT NULL;

clean = (df
    .dropna(subset=["OrderID"])
    .fillna({"Region": "Unknown", "Discount": 0}))

In Power Query, use Replace values to turn null into a default, Fill down when a value is only written on the first row of a group, Remove rows > Remove blank rows, and Replace errors for cells that failed a conversion.

Decide per column whether a null means "unknown", "zero" or "drop the row". Replacing a missing sales amount with 0 changes averages, so it is a business decision, not only a technical one.

Converting data types

Table.TransformColumnTypes(Source, {{"OrderDate", type date}, {"Amount", type number}}, "en-GB")

The culture argument "en-GB" makes Power Query read 03/04/2026 as 3 April, not 4 March. Wrong types also hurt later: a key stored as text in one table and as an integer in another does not relate cleanly in a semantic model, so give both key columns the same type.

Filtering

Table.SelectRows(Source, each [Status] <> "Cancelled" and [OrderDate] >= #date(2024, 1, 1))

from pyspark.sql import functions as F

orders = df.filter((F.col("Status") != "Cancelled") & (F.col("OrderDate") >= "2024-01-01"))

Filter as early as possible. In Power Query, early filters on a database source usually fold, which means they are sent to the source as part of its query, so less data is transferred.

A worked example

A customer file loaded into a lakehouse has 5% duplicate customers, blank regions, dates as dd/MM/yyyy text and test accounts whose names start with "TEST".

1. Profile the file on the full data set to measure each problem.
2. Rank rows per CustomerID by ModifiedDate and keep the newest one.
3. Replace blank regions with Unknown.
4. Convert the date with to_date(..., "dd/MM/yyyy") or with Change type using the en-GB locale.
5. Filter out rows whose name starts with "TEST", then write the clean table.

How the exam asks about this

• "A conversion must not fail when some values are not numbers." TRY_CAST, which returns NULL for bad values.
• "Dates such as 03/04/2026 load as the wrong month." Change type using locale with the right culture.
• "Only the first row of each group has the category filled in." Fill down in Power Query.