Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

20 min

Messy data: duplicates, missing values and nulls

Find and fix duplicate, missing and null data in Power Query, SQL and PySpark before it reaches a model.

By the end of this lesson you can

  • Find duplicates, nulls and type errors with column profiling
  • Remove duplicates in Power Query, T-SQL and PySpark, keeping the right copy
  • Choose to drop, fill or point a missing value at an Unknown member
  • Convert data types safely with change type, CAST, TRY_CAST and cast

From earlier lessons · 1 of 3

In Power Query, you combine the North and South price lists, which have the same columns. The result should have more rows, not more columns. Which command do you use?

Pick one answer.

From earlier lessons · 2 of 3

Juniper Coffee wants the till database in Azure SQL Database kept in OneLake as near real-time, read-only Delta tables. Nobody wants to build or schedule an ETL pipeline. What should you set up?

Pick one answer.

From earlier lessons · 3 of 3

A Copy job loads the till's Sales table each night with incremental copy. Rows deleted in the source must also disappear from the destination. The source has change data capture (CDC) turned on. Which method fits?

Pick one answer.

Monday at Juniper Coffee. Finance says last week's sales are 4% too high. A store manager says the region slicer shows a mystery option called (Blank). You open the till data. One café's Friday sales loaded twice. Some rows have no product category. And the supplier's UnitPrice column holds "N/A" next to real prices.

[6]

Find the problems first

Exam depth: know it cold.

Lesson 10's column profiling is your first check. It reads the top 1,000 rows unless you switch it to the entire data set.

You seeIt means.
Empty above 0% in column qualityNulls or blank values to handle.
Error above 0%Values that failed a type change.
Distinct count lower than row count on a keyDuplicate keys.

To look at the duplicates themselves, select the key column, then Home > Keep rows > Keep duplicates. Only the repeated rows stay. Delete that step once you've seen them.

[98][121]

Duplicates: keep one row per key

Exam depth: know it cold.

A duplicate is one real event stored more than once, such as Friday's sales loaded twice. Pick the fix by what the copies look like.

The copies arePower QueryT-SQLPySpark.
Identical in every columnSelect all columns, Remove duplicatesSELECT DISTINCTdropDuplicates().
Same key, any copy will doSelect the key, Remove duplicatesROW_NUMBER(), keep 1dropDuplicates(["key"]).
Same key, keep the newestNot reliableROW_NUMBER() sorted DESC, keep 1row_number() over a window.

ROW_NUMBER is a window function: it numbers rows 1, 2, 3 inside each group in the order you give. Each group is called a partition.

Decision rule: if a specific copy must win, use ROW_NUMBER ordered newest first.

[121][123][132]

sql
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY SaleID
ORDER BY LoadedAt DESC, LoadID DESC
) AS rn
FROM staging.TillSales
)
SELECT SaleID, StoreID, ProductSKU, Quantity, Amount, LoadedAt
FROM ranked
WHERE rn = 1;
T-SQL in a Warehouse: keep the newest row per sale. LoadID breaks ties.

[123]

python
from pyspark.sql import Window
from pyspark.sql.functions import col, row_number
sales = spark.read.table("bronze_till_sales")
any_copy = sales.dropDuplicates(["sale_id"])
w = Window.partitionBy("sale_id").orderBy(col("loaded_at").desc())
latest = sales.withColumn("rn", row_number().over(w)).filter("rn = 1").drop("rn")
PySpark in a Notebook: any copy, or the newest copy.

[132][137]

[121]

You are an analytics engineer at Juniper Coffee. When a till corrects a sale, it sends the whole row again with a later LoadedAt. The Warehouse staging table has several rows per SaleID. The clean table must keep only the latest version. What should you use?

Pick one answer.

[123][132]

Why this breaks a semantic model

Exam depth: know it cold.

A semantic model joins tables with relationships. In a one-to-many relationship, the "one" side, such as Store, must hold each key only once. The "many" side, such as Sales, repeats keys.

Two things go wrong with dirty data:

ProblemWhat you see.
Duplicate keys in the "one" side tableRefresh fails, or the relationship can't be created: One of the columns must have unique values.
Sales keys with no match in StoreA (Blank) item in slicers and visuals.

That (Blank) is a blank virtual row Power BI adds to the "one" side for unmatched keys. Microsoft calls it an unknown member. The fix is clean keys before the data reaches the model.

[60][130]

You are an analytics engineer at Juniper Coffee. A report's Store slicer shows a (Blank) item. The Store table has no blank StoreID values. Sales relates to Store on StoreID, one-to-many. What is the most likely cause?

Pick one answer.

[60]

Missing values: drop, fill or flag

Exam depth: know it cold.

A null means the column holds no value at all. A blank is an empty text value, "". They look alike but aren't the same.

ChoicePower QueryT-SQLPySpark.
Drop the rowFilter, or Remove emptyWHERE col IS NOT NULLdropna(subset=[...]).
Fill a fixed labelReplace values null with "Unknown"COALESCE(col, 'Unknown') or ISNULLfillna({...}).
Copy the value aboveFill > DownRarely neededRarely needed.

Remove empty drops both null and blank values. Fill down copies the last value into the nulls below it, which fixes exports where a region name appears only once per block.

ISNULL takes exactly two values. COALESCE takes any number and returns the first that isn't null.

Decision rule: drop rows you can't use, label missing text, and keep measures null.

[100][101][122][127][129][133][134]

python
clean = (
sales
.dropna(subset=["sale_id", "store_id"])
.fillna({"category": "Unknown", "promo_code": "None"})
)
Drop sales with no ID or store, then label missing text columns.

[133][134]

[122]

The Unknown member: never drop a sale

Exam depth: know it cold.

A fact table stores events, such as sales. A dimension table describes things in them, such as stores or products. Lesson 13 builds both.

When a sale's product key is missing or matches nothing, still load the sale. Dropping it makes revenue wrong. Instead, point it at a special dimension member: a row you add to the dimension on purpose.

KeyMemberMeaning.
0MissingThe source had no value.
-1UnknownThe lookup failed during the load.

In T-SQL, COALESCE(p.ProductKey, -1) after a left join does it. Reports then show a clear Unknown bucket instead of a vague (Blank).

[34][60][131]

Friday's sales, now available in double. Picture: Claymation-style scene: a cheerful clay coffee cup character holds up a receipt labeled "FRIDAY".

Convert data types without losing rows

Exam depth: know it cold.

Text that should be a number or date must be converted. Each tool reacts differently to a value it can't convert, such as "N/A".

ToolSyntaxBad value becomes.
Power QueryChange typeAn Error cell.
T-SQLCAST(x AS decimal(10,2)), CONVERT(decimal(10,2), x)The query fails.
T-SQLTRY_CAST(x AS decimal(10,2)), TRY_CONVERTNULL.
PySparkcol("x").cast("decimal(10,2)")NULL in Fabric's default settings.

CONVERT also takes a style number for date formats. Fabric Spark keeps ANSI mode off, so cast returns null rather than failing. Runtime 2.0 also offers try_cast.

Decision rule: in T-SQL, use TRY_CAST when bad values must not stop the load. Then check what turned into NULL.

[46][124][125][126][135][136][138]

sql
SELECT SupplierID, SKU, UnitPriceText
FROM staging.SupplierPrices
WHERE UnitPriceText IS NOT NULL
AND TRY_CAST(UnitPriceText AS decimal(10,2)) IS NULL;
Find the price rows that won't convert before you load them.

[125]

[99][125]

You are an analytics engineer at Juniper Coffee. A Warehouse load converts UnitPriceText to decimal. A few suppliers send "N/A", and the whole load fails. The load must finish, and those prices must end up NULL for review. What should you change?

Pick one answer.

[125][126]

Match

Match each Juniper Coffee problem to its fix

Pick an item on the left, then its match on the right.

[34][46][122]

Exam-style practice

You are an analytics engineer at Juniper Coffee. A finance Excel export lists Region only on the first row of each block. The cells below look empty. You apply Fill down on Region in Dataflow Gen2, but nothing changes. What should you do first?

Pick one answer.

[122]

You are an analytics engineer at Juniper Coffee. Semantic model refresh fails after a new product feed. Product relates to Sales on SKU, one-to-many. The feed sends "ESP-01" and "esp-01" for the same product. You need refresh to work and SKUs to stay unique. What should you do in Power Query?

Pick one answer.

[60][121]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[34][60][121][122][124][125][127]

Worth keeping

  1. Profile first: Empty, Error and distinct-vs-rows tell you what to fix.
  2. Identical copies: Remove duplicates, DISTINCT or dropDuplicates. A specific copy must win: ROW_NUMBER, newest first.
  3. Drop unusable rows, label missing text, keep measures null, and never drop a fact for a bad key.
  4. CAST fails on bad values; TRY_CAST returns NULL. Power Query shows Error cells.
  5. Duplicate one-side keys fail refresh; unmatched many-side keys show as (Blank).

On the job

Here's how the 4% gap from the hook gets closed.

In the Warehouse, the sales load now keeps the newest row per SaleID with ROW_NUMBER. Friday counts once.

The product lookup uses a left join and COALESCE(ProductKey, -1). Sales with unknown SKUs land on the Unknown product instead of vanishing.

The supplier Dataflow Gen2 replaces "N/A" with null, then sets UnitPrice to Fixed decimal number. A Keep errors copy of the query runs once a month and goes to the buying team.

The store manager's slicer now shows Unknown with three sales. Everyone knows what it means and who fixes it.

[131]

The clean-up logic works, but it's pasted into three different queries. When the rule changes, someone will miss one. Next lesson: put it in one place with views, functions and stored procedures.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (27)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [34]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
  3. [40]Microsoft Learn: What is Mirroring in Fabric?
  4. [46]Microsoft Learn: Dealing with errors in Power Query
  5. [60]Microsoft Learn: Model relationships in Power BI Desktop
  6. [94]Microsoft Learn: What is Copy job in Data Factory for Microsoft Fabric?
  7. [98]Microsoft Learn: Using the data profiling tools
  8. [99]Microsoft Learn: Data types in Power Query
  9. [100]Microsoft Learn: Filter by values in a column
  10. [101]Microsoft Learn: Replace values and errors
  11. [121]Microsoft Learn: Working with duplicate values
  12. [122]Microsoft Learn: Fill values in a column
  13. [123]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
  14. [124]Microsoft Learn: Migrate from Fabric Runtime 1.3 to Runtime 2.0
  15. [125]Microsoft Learn: TRY_CAST (Transact-SQL)
  16. [126]Microsoft Learn: CAST and CONVERT (Transact-SQL)
  17. [127]Microsoft Learn: COALESCE (Transact-SQL)
  18. [129]Microsoft Learn: ISNULL (Transact-SQL)
  19. [130]Microsoft Learn: Create and manage relationships in Power BI Desktop
  20. [131]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Load tables
  21. [132]Apache Spark: pyspark.sql.DataFrame.dropDuplicates
  22. [133]Apache Spark: pyspark.sql.DataFrame.fillna
  23. [134]Apache Spark: pyspark.sql.DataFrame.dropna
  24. [135]Apache Spark: pyspark.sql.Column.cast
  25. [136]Apache Spark: pyspark.sql.Column.try_cast
  26. [137]Apache Spark: pyspark.sql.functions.row_number
  27. [138]Apache Spark: ANSI compliance