All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
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.
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 see | It means. |
|---|---|
| Empty above 0% in column quality | Nulls or blank values to handle. |
| Error above 0% | Values that failed a type change. |
| Distinct count lower than row count on a key | Duplicate 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.
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 are | Power Query | T-SQL | PySpark. |
|---|---|---|---|
| Identical in every column | Select all columns, Remove duplicates | SELECT DISTINCT | dropDuplicates(). |
| Same key, any copy will do | Select the key, Remove duplicates | ROW_NUMBER(), keep 1 | dropDuplicates(["key"]). |
| Same key, keep the newest | Not reliable | ROW_NUMBER() sorted DESC, keep 1 | row_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.
WITH ranked AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY SaleIDORDER BY LoadedAt DESC, LoadID DESC) AS rnFROM staging.TillSales)SELECT SaleID, StoreID, ProductSKU, Quantity, Amount, LoadedAtFROM rankedWHERE rn = 1;
from pyspark.sql import Windowfrom pyspark.sql.functions import col, row_numbersales = 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")
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.
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:
| Problem | What you see. |
|---|---|
| Duplicate keys in the "one" side table | Refresh fails, or the relationship can't be created: One of the columns must have unique values. |
| Sales keys with no match in Store | A (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.
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.
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.
| Choice | Power Query | T-SQL | PySpark. |
|---|---|---|---|
| Drop the row | Filter, or Remove empty | WHERE col IS NOT NULL | dropna(subset=[...]). |
| Fill a fixed label | Replace values null with "Unknown" | COALESCE(col, 'Unknown') or ISNULL | fillna({...}). |
| Copy the value above | Fill > Down | Rarely needed | Rarely 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.
clean = (sales.dropna(subset=["sale_id", "store_id"]).fillna({"category": "Unknown", "promo_code": "None"}))
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.
| Key | Member | Meaning. |
|---|---|---|
| 0 | Missing | The source had no value. |
| -1 | Unknown | The 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).
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".
| Tool | Syntax | Bad value becomes. |
|---|---|---|
| Power Query | Change type | An Error cell. |
| T-SQL | CAST(x AS decimal(10,2)), CONVERT(decimal(10,2), x) | The query fails. |
| T-SQL | TRY_CAST(x AS decimal(10,2)), TRY_CONVERT | NULL. |
| PySpark | col("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.
SELECT SupplierID, SKU, UnitPriceTextFROM staging.SupplierPricesWHERE UnitPriceText IS NOT NULLAND TRY_CAST(UnitPriceText AS decimal(10,2)) IS NULL;
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.
Match
Match each Juniper Coffee problem to its fix
Pick an item on the left, then its match on the right.
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.
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.
Worth keeping
- Profile first: Empty, Error and distinct-vs-rows tell you what to fix.
- Identical copies: Remove duplicates, DISTINCT or dropDuplicates. A specific copy must win: ROW_NUMBER, newest first.
- Drop unusable rows, label missing text, keep measures null, and never drop a fact for a bad key.
- CAST fails on bad values; TRY_CAST returns NULL. Power Query shows Error cells.
- 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.
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.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [34]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables
- [40]Microsoft Learn: What is Mirroring in Fabric?
- [46]Microsoft Learn: Dealing with errors in Power Query
- [60]Microsoft Learn: Model relationships in Power BI Desktop
- [94]Microsoft Learn: What is Copy job in Data Factory for Microsoft Fabric?
- [98]Microsoft Learn: Using the data profiling tools
- [99]Microsoft Learn: Data types in Power Query
- [100]Microsoft Learn: Filter by values in a column
- [101]Microsoft Learn: Replace values and errors
- [121]Microsoft Learn: Working with duplicate values
- [122]Microsoft Learn: Fill values in a column
- [123]Microsoft Learn: Analytical and aggregate functions in Fabric Data Warehouse
- [124]Microsoft Learn: Migrate from Fabric Runtime 1.3 to Runtime 2.0
- [125]Microsoft Learn: TRY_CAST (Transact-SQL)
- [126]Microsoft Learn: CAST and CONVERT (Transact-SQL)
- [127]Microsoft Learn: COALESCE (Transact-SQL)
- [129]Microsoft Learn: ISNULL (Transact-SQL)
- [130]Microsoft Learn: Create and manage relationships in Power BI Desktop
- [131]Microsoft Learn: Dimensional modeling in Microsoft Fabric Warehouse: Load tables
- [132]Apache Spark: pyspark.sql.DataFrame.dropDuplicates
- [133]Apache Spark: pyspark.sql.DataFrame.fillna
- [134]Apache Spark: pyspark.sql.DataFrame.dropna
- [135]Apache Spark: pyspark.sql.Column.cast
- [136]Apache Spark: pyspark.sql.Column.try_cast
- [137]Apache Spark: pyspark.sql.functions.row_number
- [138]Apache Spark: ANSI compliance