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)
22 min
Power Query in Dataflow Gen2: filter, data types, merge and new columns
Shape data in Power Query: filter rows, set types, merge and append queries, add columns.
By the end of this lesson you can
- Find your way around the Power Query editor and read its column profiles
- Filter rows, remove columns, set data types and replace values
- Choose between merge and append, and pick the right join kind
- Add custom, conditional and example-based columns, and group rows
From earlier lessons · 1 of 2
Juniper Coffee's finance team can't write code. They need to load supplier price-list CSVs into a lakehouse every week and clean them on the way in. Which Fabric item fits best?
Pick one answer.
From earlier lessons · 2 of 2
Juniper Coffee wants a lakehouse to read files that already sit in an Azure Data Lake Storage account, without copying them. What should you create?
Pick one answer.
Juniper Coffee buys beans from four regional suppliers. Each one emails a price-list CSV every month. Finance wants one table that shows what each café product costs to make. The lists use different column orders, some prices say "N/A", and the product codes live in the till database. You open Dataflow Gen2 to stitch it together.
The Power Query editor
Exam depth: know it cold.
Power Query is the no-code tool for shaping data, used in Dataflow Gen2 and Power BI Desktop. Dataflow Gen2 runs the browser version, called Power Query Online.
The editor has five parts:
| Part | What it shows. |
|---|---|
| Ribbon | Tabs of transforms: Home, Transform, Add column, View. |
| Queries pane | Every query in the dataflow. A query is one table you're shaping. |
| Current view | The data preview: a sample of the rows after your steps. |
| Query settings | The query name and its Applied steps. |
| Status bar | Row and column counts and the profiling message. |
Each click adds an applied step. Select a step to see the data at that point. Behind the steps sits code in M, the Power Query formula language. Advanced editor on the View tab shows it.
Column profiling: see problems before you fix them
Exam depth: know it cold.
Column profiling adds small charts that describe each column. There are three tools:
| Tool | What it tells you. |
|---|---|
| Column quality | Percent of values that are Valid, Error or Empty. |
| Column distribution | A bar chart of value counts, plus distinct and unique counts. |
| Column profile | A details pane with statistics and a value chart for one column. |
Distinct is how many different values a column has. Unique is how many values appear only once. A key column should have as many distinct values as rows.
In Dataflow Gen2, turn them on in Home > Options > Global options, under Column profile.
Filter rows, remove columns, set types, replace values
Exam depth: know it cold.
These four steps do most of the work:
| Step | Where | What it does. |
|---|---|---|
| Filter rows | Arrow on the column header | Keeps rows that match. Remove empty drops null and blank values. |
| Choose or remove columns | Home > Manage columns | Keeps only the columns you need. |
| Change type | Icon left of the column name | Sets the data type, such as Whole number or Date. |
| Replace values | Transform > Any column | Swaps one value for another, such as "N/A" for null. |
A data type says what a column holds: text, numbers, dates. Money needs Fixed decimal number, also called Currency. It keeps four decimal places exactly. Decimal number is a floating-point type and can round slightly.
Avoid leaving any column as Any, the "no type set" type.
letSource = PricesNorth,Typed = Table.TransformColumnTypes(Source,{{"UnitPrice", Currency.Type}, {"ValidFrom", type date}}),Beans = Table.SelectRows(Typed,each [Category] = "Beans" and [UnitPrice] > 0),Kept = Table.SelectColumns(Beans, {"SKU", "UnitPrice", "ValidFrom"})inKept
You are an analytics engineer at Juniper Coffee. After you set UnitPrice to Fixed decimal number, column quality shows 2% Error. Before you pick a fix, the supplier wants to see exactly which rows failed. What should you do?
Pick one answer.
Merge or append: side by side, or stacked
Exam depth: know it cold.
Both live on Home > Combine. They solve different problems.
| Merge queries | Append queries. | |
|---|---|---|
| Result | Adds columns from a second table | Adds rows from other tables. |
| Needs | A matching key column | Columns with the same names. |
| SQL cousin | JOIN | UNION ALL. |
| Juniper example | Add supplier cost to each product | Stack four regional price lists. |
Append matches columns by name, not position. A column missing from one table is filled with null.
Each has an as new option. Merge queries as new and Append queries as new build a new query and leave the originals unchanged. Append also has a Three or more tables mode.
Decision rule: more columns means merge; more rows means append.
Exam depth: know it cold.
The join kind decides which rows survive a merge. The left table is the first one in the dialog. The right table is the second.
| Join kind | Keeps. |
|---|---|
| Left outer (default) | All left rows, plus matches from the right. |
| Right outer | All right rows, plus matches from the left. |
| Full outer | All rows from both tables. |
| Inner | Only rows that match in both. |
| Left anti | Only left rows with no match. |
| Right anti | Only right rows with no match. |
Anti joins find what's missing. Products left, prices right, left anti: products that no supplier prices.
A merge adds one column of nested tables. You then expand it to pick the right table's columns.
letAllPrices = Table.Combine({PricesNorth, PricesSouth, PricesEast, PricesWest}),Merged = Table.NestedJoin(Products, {"SKU"}, AllPrices, {"SKU"},"Prices", JoinKind.LeftOuter),Expanded = Table.ExpandTableColumn(Merged, "Prices",{"UnitPrice"}, {"UnitCost"})inExpanded
You are an analytics engineer at Juniper Coffee. In Dataflow Gen2, Products is the left table and AllPrices is the right table, joined on SKU. Finance needs a list of only the products that no supplier price list includes. Which join kind should you choose?
Pick one answer.
Add columns and group rows
Exam depth: know it cold.
Four ways to enrich a table, all on the Add column tab except Group by:
| Tool | Use it when | Watch out. |
|---|---|---|
| Custom column | You can write the formula, such as [Price] - [UnitCost] | It's M. Online lets you set the type. |
| Conditional column | Simple if-then-else rules, such as size from cup volume | Clauses test top to bottom. No type is set. |
| Column from examples | You type the result you want; Power Query finds the steps | It looks at only the top 100 rows. |
| Group by | One row per group with Sum, Average, Count rows and more | Rows below group level disappear. |
A custom column adds Table.AddColumn(Expanded, "Margin", each [Price] - [UnitCost], Currency.Type).
Decision rule: no formula skills means conditional column or column from examples.
Match
Match each Juniper Coffee need to the Power Query tool
Pick an item on the left, then its match on the right.
Reference, duplicate and Enable load
Exam depth: recognize it.
Right-click a query in the Queries pane to reuse it two ways:
| Reference | Duplicate. | |
|---|---|---|
| You get | A new query that starts from the original's output | A full copy of every step. |
| Later edits to the original | Flow into the new query | Don't reach the copy. |
A reference doesn't save work at refresh. Each query that references the original runs its steps again.
A staging query is a helper that other queries build on, such as the raw price list. It shouldn't become a table of its own. In Power Query, right-click it and clear Enable load; it then shows a gray outline. In Dataflow Gen2, a query lands in a lakehouse or warehouse only if it has a data destination, so give helpers none.
Decision rule: one shared start that must stay in sync means reference; a separate experiment means duplicate.
Query folding: let the source do the work
Exam depth: recognize it.
Query folding turns your steps into one query the source runs itself, such as SQL for the till database. Only the result travels back, so refreshes run faster.
It depends on the source. Databases such as Azure SQL Database usually fold. CSV and Excel files have no query engine, so they never fold.
In Power Query Online, an icon beside each step shows whether the query folds up to that point. Once a step stops folding, later steps usually don't fold either.
Decision rule: put filters and column removals early, before any step that breaks folding.
Try it in Fabric
A free Fabric trial is enough. This uses Microsoft's public sample feed.
- In your workspace, select +New item > Dataflow Gen2.
- Select Get data > More, then View more > Other > OData.
- Enter
https://services.odata.org/v4/northwind/northwind.svc/, select Next, pick Orders and Customers, then Create. - Turn on profiling: Home > Options > Global options, check everything under Column profile.
- In Orders, select CustomerID, then Transform > Group by, with Count rows.
- Select Customers, then Merge queries as new. Match CustomerID in both, keep Left outer, select OK.
- Expand the new column and pick Count. Look at Applied steps to see each step you made.
Exam-style practice
You are an analytics engineer at Juniper Coffee. A till export has 400,000 rows. Column quality shows StoreID as 100% valid, but the loaded table has empty StoreID values. You need the profile to reflect every row. What should you do?
Pick one answer.
You are an analytics engineer at Juniper Coffee. A Dataflow Gen2 reads five years of sales from Azure SQL Database. Step 3 capitalizes product names and shows a not-folding icon. Step 4 filters to the last 30 days. Refresh is slow. You need it faster with the same output. What should you do?
Pick one answer.
You are an analytics engineer at Juniper Coffee. Four regional price-list queries have the same columns in different orders. You need one combined table and must keep the four original queries unchanged. What should you do?
Pick one answer.
Worth keeping
- Every click is an applied step written in M. Read the steps to know what a query does.
- Column profiling reads only the top 1,000 rows unless you switch it to the entire data set.
- Merge adds columns by a key; append adds rows by column name.
- Pick the join kind from which rows must survive. Anti joins find what's missing.
- Keep folding steps early so the source does the heavy work.
The price table is built. But some till rows appear twice, some products have no category, and one café's sales show up under (Blank). Next lesson: hunting down duplicates, missing values and nulls in Power Query, SQL and PySpark.
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 (29)
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
- [46]Microsoft Learn: Dealing with errors in Power Query
- [83]Microsoft Learn: OneLake shortcuts
- [92]Microsoft Learn: Dataflow Gen2 data destinations and managed settings
- [93]Microsoft Learn: Quickstart: Create your first dataflow to get and transform data
- [97]Microsoft Learn: Use Power Query to transform data
- [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
- [102]Microsoft Learn: Choose or remove columns
- [103]Microsoft Learn: Merge queries overview
- [104]Microsoft Learn: Append queries
- [105]Microsoft Learn: Add a custom column
- [106]Microsoft Learn: Add a conditional column
- [107]Microsoft Learn: Add a column from examples
- [108]Microsoft Learn: Grouping or summarizing rows
- [109]Microsoft Learn: Overview of query evaluation and query folding in Power Query
- [110]Microsoft Learn: Query folding indicators
- [111]Microsoft Learn: Table.SelectRows
- [112]Microsoft Learn: Table.TransformColumnTypes
- [113]Microsoft Learn: Table.NestedJoin
- [114]Microsoft Learn: Table.ExpandTableColumn
- [115]Microsoft Learn: Table.Combine
- [116]Microsoft Learn: Table.AddColumn
- [117]Microsoft Learn: JoinKind.Type
- [118]Microsoft Learn: Using the Queries pane
- [119]Microsoft Learn: Diagram view
- [120]Microsoft Learn: Referencing Power Query queries