Transforming and Cleansing Data

Introduction

This lesson continues the domain Prepare data (45–50% of the DP-600 skills measured). This course targets the English syllabus effective October 19, 2026. Microsoft does not publish the question count or the question-type mix for the exam, so OnLevelUp mock exams use a 50-question practice length and an unofficial 80% practice target; neither is equivalent to the official 700/1000 scaled passing score.

Raw data is rarely fit for analysis: duplicates inflate totals, nulls break joins, text-typed numbers refuse to sum, and unfiltered rows pollute reports. The exam describes a data problem and asks which tool (Dataflow Gen2, notebook, T-SQL, KQL) and which operation solves it with the right trade-off. After this lesson you will be able to aggregate, merge and join, detect and remove duplicates, handle missing data and nulls, convert data types and filter data, in Power Query, T-SQL and PySpark, and choose the right tool.

Learning objectives

• Aggregate data with group-by operations in Power Query, T-SQL and PySpark.
• Choose the right join type and merge or append datasets.
• Identify and resolve duplicates, missing data and nulls.
• Convert data types safely and filter rows and columns early.
• Choose a transformation tool and understand query folding.

1. Choosing a transformation tool

Query folding. In Power Query, folding means the steps are translated into a single query pushed to the source (for example SQL). Filters, column selection, group-by and joins often fold; custom functions, adding an index column or some type conversions often break folding, and later steps run in the mashup engine, which is slower. Keep foldable steps first. In Dataflows Gen2, staging can also improve performance by loading data to a staging lakehouse before transformation.

2. Aggregating data

An aggregation reduces rows to summary values (sum, count, average, min, max, distinct count) per group.

Power Query: Home → Group by, then choose one or more key columns and operations. The M code is Table.Group.

T-SQL:

SELECT  CustomerKey,
        YEAR(OrderDate) AS OrderYear,
        SUM(Quantity * UnitPrice) AS Revenue,
        COUNT(DISTINCT OrderID)   AS Orders
FROM    dbo.FactSales
GROUP BY CustomerKey, YEAR(OrderDate)
HAVING  SUM(Quantity * UnitPrice) > 10000;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

PySpark:

from pyspark.sql import functions as F
agg = (df.groupBy("CustomerKey", F.year("OrderDate").alias("OrderYear"))
         .agg(F.sum(F.col("Quantity")*F.col("UnitPrice")).alias("Revenue"),
              F.countDistinct("OrderID").alias("Orders")))

When to pre-aggregate: only if every consumer needs the coarser grain (a monthly summary table). Otherwise keep detail and aggregate in the semantic model, so users can drill down.

Example. Northwind keeps 400 million order lines. A daily sales dashboard needs only totals by store and day. A summary table AggSalesStoreDay of 2 million rows serves it, while the detailed fact stays for investigations.

3. Merging and joining data

Combining datasets comes in two forms: horizontal (join/merge: add columns) and vertical (union/append: add rows).

-- customers who never ordered (left anti)
SELECT c.CustomerKey
FROM dbo.DimCustomer c
LEFT JOIN dbo.FactSales s ON s.CustomerKey = c.CustomerKey
WHERE s.CustomerKey IS NULL;

orders.join(customers, on="CustomerKey", how="left_anti")

Points to remember:

• Join on keys of the same data type and format; a text "001" does not match the integer 1.
• Joins on a column with duplicates multiply rows (a many-to-many join). Check key uniqueness first.
• Append (UNION ALL in SQL, unionByName in Spark, Append queries in Power Query) stacks datasets with the same columns. UNION removes duplicates; UNION ALL keeps them and is faster.
• In Power Query, Merge queries creates a nested column that you expand; choose only the needed columns.

Worked example 4: choosing the join. Statement. A retailer has Sales (5 million rows) and Stores (400 rows). Some sales rows reference store IDs missing from Stores. The analyst wants to keep every sale and show "Unknown" when the store is missing. Reasoning. Every sale must be kept, so Sales is the preserved side: a left outer join from Sales to Stores. Missing stores produce nulls in store columns, which are then replaced with "Unknown". Solution. SELECT s.*, COALESCE(st.StoreName,'Unknown') AS StoreName FROM Sales s LEFT JOIN Stores st ON st.StoreID = s.StoreID. Why the other choices are worse. An inner join silently drops sales with unknown stores, understating revenue. A full outer join adds stores that never sold, which are not needed. A right outer join preserves the wrong table.

4. Duplicates

Duplicates arise from repeated loads, overlapping extracts, double events and many-to-many joins. First define what makes a row a duplicate (all columns, or a business key such as OrderID).

Power Query: select the key columns → Remove duplicates (or Keep duplicates to inspect them). It keeps the first occurrence it encounters, so sort first if you need the latest row; note that sorting may break folding and the retained row can be non-deterministic if the sort is not buffered.

T-SQL (keep the latest row per key):

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ModifiedAt DESC) AS rn
  FROM stg.Orders
)
SELECT * FROM ranked WHERE rn = 1;

PySpark:

from pyspark.sql import Window, functions as F
w = Window.partitionBy("OrderID").orderBy(F.col("ModifiedAt").desc())
dedup = df.withColumn("rn", F.row_number().over(w)).filter("rn = 1").drop("rn")
# exact duplicates only
df.dropDuplicates()               # all columns
df.dropDuplicates(["OrderID"])    # by key (arbitrary survivor)

Prefer ROW_NUMBER with an explicit order when the survivor matters; dropDuplicates(["key"]) keeps an arbitrary row.

5. Missing data and nulls

NULL means unknown or not applicable; an empty string and zero are different things. Nulls propagate: NULL + 5 is NULL, NULL = NULL is not true, and aggregates such as SUM and AVG ignore nulls (so AVG of three values with one null divides by two).

Strategies:

SELECT COALESCE(Region, 'Unknown') AS Region,
       ISNULL(Discount, 0)         AS Discount,
       NULLIF(Quantity, 0)         AS SafeQuantity
FROM dbo.Sales;

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

In Power Query: Replace values (replace null with a value, noting that blanks and nulls differ for text), Fill → Down, Remove empty, and Replace errors.

Distinguish errors from nulls: a failed conversion creates an error in Power Query (Table.RemoveRowsWithErrors, Replace errors) or a failure/NULL in SQL (TRY_CAST returns NULL instead of failing).

Example. A hospital's patient table has DischargeDate null for current inpatients. Replacing it with 1900-01-01 would produce absurd stays. Keeping null and computing length of stay with COALESCE(DischargeDate, CAST(GETDATE() AS date)) keeps the meaning.

6. Converting data types

Correct types matter for correctness, compression and relationships.

• Dates stored as text ("12/03/2026") are ambiguous: the locale decides whether it is 12 March or 3 December. In Power Query use Using Locale when changing type; in SQL use TRY_CONVERT(date, col, 103) with a style; in Spark to_date(col, "dd/MM/yyyy").
• Numbers as text with currency symbols or thousands separators need cleaning before casting.
• Decimal vs float: use fixed-precision decimal(18,2) for money; floating point can create rounding noise.
• Choose the smallest suitable type (int vs bigint, date vs datetime) for compression and speed.

SELECT TRY_CAST(Amount AS decimal(18,2)) AS Amount,
       TRY_CONVERT(date, OrderDateText, 103) AS OrderDate
FROM stg.Orders;

df = (df.withColumn("Amount", F.col("Amount").cast("decimal(18,2)"))
        .withColumn("OrderDate", F.to_date("OrderDateText", "dd/MM/yyyy")))

Change types after cleaning text (trim, replace commas), and handle failures with TRY_ functions or error-handling steps so one bad row does not fail the entire load.

7. Filtering data

Filter rows and columns as early as possible.

• Remove rows that are never needed (test records, cancelled orders, dates before the business start).
• Remove unused columns: fewer columns means smaller models and faster refresh.
• In Power Query, filter steps fold to the source, so the database does the work.
• In Spark, filters on partition columns allow partition pruning; predicate pushdown applies to Delta and Parquet.
• Use a parameter (for example a start date) for dynamic filtering and incremental refresh later.

SELECT OrderID, OrderDate, Amount
FROM dbo.Orders
WHERE OrderDate >= '2024-01-01' AND Status <> 'Cancelled';

Mind three-valued logic: WHERE Status <> 'Cancelled' also removes rows where Status is NULL. If nulls must be kept, write WHERE Status <> 'Cancelled' OR Status IS NULL.

Worked example 5: a cleaning sequence. Statement. A Dataflow Gen2 reads a 30-million-row Orders table from Azure SQL Database. Rows include test orders (CustomerID = 0), duplicate OrderIDs from a double extract, text amounts like "1,250.50", and 2% of rows missing Region. The refresh is slow and the final dataflow must be correct. Reasoning. (1) Filter test rows and unused columns first, so the filter folds and fewer rows travel. (2) Remove duplicates on OrderID, after ordering by ModifiedAt descending if the newest should survive. (3) Clean the text amount (remove the thousands separator with the right locale) then convert to decimal. (4) Replace null Region with "Unknown" and add a flag if the missing count matters. Solution. Filter → remove columns → sort and deduplicate → clean text → change type (with locale) → replace nulls. Why not another order. Converting types before cleaning text produces errors; deduplicating before filtering wastes work; doing everything in the report would repeat the cost on each refresh.

8. Putting it in the right tool: decision table

9. Equivalent operations across languages

Knowing the equivalence lets you answer a question in any of the three languages. A frequent trap is a scenario stating the team's skills: choose the tool they already master, provided it meets the volume requirement. Another is idempotency: a transformation that is rerun must not duplicate rows, so loads should overwrite a partition, merge on a key, or deduplicate.

Common pitfalls

• Using an inner join and losing rows without noticing.
• Joining on columns with different types or hidden whitespace.
• Treating NULL like zero or an empty string.
• Removing duplicates without defining the key or order.
• Converting dates without specifying the locale.
• Filtering after the expensive steps and breaking query folding.
• Writing NOT IN against a column that contains NULLs, which returns no rows.

Exam tip: when a scenario says "keep all records from X", think left (or right) outer join. When it says "records in X with no match in Y", think anti join. When it says "performance of a Dataflow", think query folding, filtering early and staging.

Mini case study

Wingtip Toys loads orders from two regional ERPs into a lakehouse. Problems reported: revenue is 7% too high, some customers appear twice with different capitalisations, 4% of orders have no sales region, and the date column is text in two different formats.

Proposed solution.

1. Trace the 7% overstatement: orders overlap between both extracts. Append the two sources with unionByName, then deduplicate by OrderID using ROW_NUMBER ordered by the latest ModifiedAt.
2. Standardise customer names with trim and upper before joining to the customer dimension; use a left outer join to keep all orders and replace missing matches with "Unknown".
3. Replace null regions with "Unknown" and add IsRegionMissing so the data steward can fix the source.
4. Parse each date format separately (to_date with the right pattern per source) before the union.
5. Filter cancelled orders and select only the columns used downstream.

Commentary. The revenue error came from duplicates, not from the model. Fixing the key and the order of operations in the pipeline stops the problem at the source rather than patching reports.

Key terms

• Aggregation: reducing rows to summary values per group.
• Join / merge: combining datasets horizontally.
• Append / union: combining datasets vertically.
• Anti join: rows with no match in the other table.
• Deduplication: keeping one row per defined key.
• NULL: unknown or absent value.
• TRY_CAST / TRY_CONVERT: conversions that return NULL on failure.
• Query folding: pushing Power Query steps to the source.
• Partition pruning: skipping partitions that cannot match a filter.

Summary checklist

• I can aggregate with Power Query, T-SQL and PySpark and distinguish WHERE from HAVING.
• I can choose between inner, outer, anti and cross joins and between UNION and UNION ALL.
• I can deduplicate with an explicit key and order.
• I know the strategies for nulls and the effect of nulls on aggregates and comparisons.
• I can convert text to dates and numbers safely with locale and TRY_ functions.
• I filter rows and columns early and preserve query folding.
• I remember that the practice length (50 questions) and 80% target are not official, and that the course targets the syllabus effective October 19, 2026.

Official sources

• Study guide for Exam DP-600: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-600 (opens in a new tab)
• Dataflow Gen2 overview: https://learn.microsoft.com/en-us/fabric/data-factory/dataflows-gen2-overview (opens in a new tab)
• Query folding in Power Query: https://learn.microsoft.com/en-us/power-query/query-folding-basics (opens in a new tab)
• Transact-SQL reference for Fabric Data Warehouse: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area (opens in a new tab)