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

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.

[6]

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:

PartWhat it shows.
RibbonTabs of transforms: Home, Transform, Add column, View.
Queries paneEvery query in the dataflow. A query is one table you're shaping.
Current viewThe data preview: a sample of the rows after your steps.
Query settingsThe query name and its Applied steps.
Status barRow 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.

[93][97][109]

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:

ToolWhat it tells you.
Column qualityPercent of values that are Valid, Error or Empty.
Column distributionA bar chart of value counts, plus distinct and unique counts.
Column profileA 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.

[93][98]

The Dataflow Gen2 Options dialog. All four Column profile boxes are checked. Below them, Column profile evaluation is set to Based on top 1,000 rows; the other choice is Based on entire data set.
Microsoft Learn, CC BY 4.0

[93]

[98]

Filter rows, remove columns, set types, replace values

Exam depth: know it cold.

These four steps do most of the work:

StepWhereWhat it does.
Filter rowsArrow on the column headerKeeps rows that match. Remove empty drops null and blank values.
Choose or remove columnsHome > Manage columnsKeeps only the columns you need.
Change typeIcon left of the column nameSets the data type, such as Whole number or Date.
Replace valuesTransform > Any columnSwaps 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.

[99][100][101][102]

powerquery
let
Source = 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"})
in
Kept
The M behind three clicks: set types, keep bean rows with a price, keep three columns.

[111][112]

[46][99]

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.

[46]

Merge or append: side by side, or stacked

Exam depth: know it cold.

Both live on Home > Combine. They solve different problems.

Merge queriesAppend queries.
ResultAdds columns from a second tableAdds rows from other tables.
NeedsA matching key columnColumns with the same names.
SQL cousinJOINUNION ALL.
Juniper exampleAdd supplier cost to each productStack 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.

[103][104][115]

The Merge dialog: Customers is the left table, Orders the right, both keyed on CustomerID. Six join kind tiles follow, Left outer selected, then Right outer, Full outer, Inner, Left anti, Right anti. It says 89 of 91 rows match.
Microsoft Learn, CC BY 4.0

[93]

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 kindKeeps.
Left outer (default)All left rows, plus matches from the right.
Right outerAll right rows, plus matches from the left.
Full outerAll rows from both tables.
InnerOnly rows that match in both.
Left antiOnly left rows with no match.
Right antiOnly 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.

[103][113][117]

powerquery
let
AllPrices = Table.Combine({PricesNorth, PricesSouth, PricesEast, PricesWest}),
Merged = Table.NestedJoin(Products, {"SKU"}, AllPrices, {"SKU"},
"Prices", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Prices",
{"UnitPrice"}, {"UnitCost"})
in
Expanded
Append four price lists, merge them onto products, then expand one column.

[113][114][115]

[103][113]

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.

[103]

Append stacks the tables. Merge lines them up. Mixing them up makes a very tall mess. Picture: Vintage instruction-manual drawing: two panels with numbered arrows.

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:

ToolUse it whenWatch out.
Custom columnYou can write the formula, such as [Price] - [UnitCost]It's M. Online lets you set the type.
Conditional columnSimple if-then-else rules, such as size from cup volumeClauses test top to bottom. No type is set.
Column from examplesYou type the result you want; Power Query finds the stepsIt looks at only the top 100 rows.
Group byOne row per group with Sum, Average, Count rows and moreRows 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.

[105][106][107][108][116]

Match

Match each Juniper Coffee need to the Power Query tool

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

[105][106][107][108]

Reference, duplicate and Enable load

Exam depth: recognize it.

Right-click a query in the Queries pane to reuse it two ways:

ReferenceDuplicate.
You getA new query that starts from the original's outputA full copy of every step.
Later edits to the originalFlow into the new queryDon'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.

[92][118][119][120]

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.

[109][110]

Try it in Fabric

A free Fabric trial is enough. This uses Microsoft's public sample feed.

  1. In your workspace, select +New item > Dataflow Gen2.
  2. Select Get data > More, then View more > Other > OData.
  3. Enter https://services.odata.org/v4/northwind/northwind.svc/, select Next, pick Orders and Customers, then Create.
  4. Turn on profiling: Home > Options > Global options, check everything under Column profile.
  5. In Orders, select CustomerID, then Transform > Group by, with Count rows.
  6. Select Customers, then Merge queries as new. Match CustomerID in both, keep Left outer, select OK.
  7. Expand the new column and pick Count. Look at Applied steps to see each step you made.

[93]

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.

[98]

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.

[109][110]

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.

[104]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 17

[46][98][103][104][106][107][109][113]

Worth keeping

  1. Every click is an applied step written in M. Read the steps to know what a query does.
  2. Column profiling reads only the top 1,000 rows unless you switch it to the entire data set.
  3. Merge adds columns by a key; append adds rows by column name.
  4. Pick the join kind from which rows must survive. Anti joins find what's missing.
  5. 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.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [46]Microsoft Learn: Dealing with errors in Power Query
  3. [83]Microsoft Learn: OneLake shortcuts
  4. [92]Microsoft Learn: Dataflow Gen2 data destinations and managed settings
  5. [93]Microsoft Learn: Quickstart: Create your first dataflow to get and transform data
  6. [97]Microsoft Learn: Use Power Query to transform data
  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. [102]Microsoft Learn: Choose or remove columns
  12. [103]Microsoft Learn: Merge queries overview
  13. [104]Microsoft Learn: Append queries
  14. [105]Microsoft Learn: Add a custom column
  15. [106]Microsoft Learn: Add a conditional column
  16. [107]Microsoft Learn: Add a column from examples
  17. [108]Microsoft Learn: Grouping or summarizing rows
  18. [109]Microsoft Learn: Overview of query evaluation and query folding in Power Query
  19. [110]Microsoft Learn: Query folding indicators
  20. [111]Microsoft Learn: Table.SelectRows
  21. [112]Microsoft Learn: Table.TransformColumnTypes
  22. [113]Microsoft Learn: Table.NestedJoin
  23. [114]Microsoft Learn: Table.ExpandTableColumn
  24. [115]Microsoft Learn: Table.Combine
  25. [116]Microsoft Learn: Table.AddColumn
  26. [117]Microsoft Learn: JoinKind.Type
  27. [118]Microsoft Learn: Using the Queries pane
  28. [119]Microsoft Learn: Diagram view
  29. [120]Microsoft Learn: Referencing Power Query queries