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)
18 min
Query with the Visual query editor and SQL
Select, filter and aggregate data in the Visual query editor and the SQL query editor.
By the end of this lesson you can
- Build a select, filter and group query in the Visual query editor
- Write the same query in the SQL query editor with T-SQL
- Save a query as a view or table, and see the SQL behind a visual query
- Query across a lakehouse and a warehouse with three-part names
From earlier lessons · 1 of 2
In Power Query, a step shows a not-folding icon. What does that tell you?
Pick one answer.
From earlier lessons · 2 of 2
In a star schema, which table holds one row per sale with keys and amounts?
Pick one answer.
Juniper Coffee's finance analyst wants weekly sales per region from the Warehouse. She knows Excel, not SQL. Down the hall, the store operations lead writes T-SQL every day and wants the top ten cafés by revenue. Both open the same Warehouse. Fabric gives each of them an editor that fits.
Two editors, one warehouse
Exam depth: know it cold.
Both editors work in a Warehouse, a lakehouse's SQL analytics endpoint, and a Mirrored database. Open the item, then use the ribbon: New SQL query or New visual query.
| Visual query editor | SQL query editor. | |
|---|---|---|
| You work by | Dragging tables and clicking Power Query steps | Typing T-SQL. |
| Statements | Read-only SELECT only | SELECT, plus DDL, DML and DCL. |
| Best for | No-code users | SQL users, and anything that changes data. |
| Output | Results, View SQL, Save as view, Save as table, Download Excel file | Results, Save as view, Save as table, Open in Excel. |
DDL creates objects, such as CREATE VIEW. DML changes rows, such as UPDATE. DCL sets permissions, such as GRANT.
On a lakehouse's SQL analytics endpoint, tables are read-only in both editors. Change their data with Spark in the lakehouse.
Decision rule: no SQL skills means the Visual query editor; any change to data means the SQL query editor.
The Visual query editor
Exam depth: know it cold.
The Visual query editor builds a query with no code. It uses the Power Query diagram view from lesson 10.
- Select New visual query.
- Drag a table from the Explorer onto the canvas. Or right-click it and select Insert to canvas.
- Add steps from the toolbar: Manage columns to choose columns, Reduce rows to filter, Transform > Group by to aggregate, Combine to merge.
- To join two tables, use Merge queries as new, pick the key columns and a join kind.
The query saves itself every few seconds into My queries. Workspace viewers can't move queries to Shared queries.
Exam depth: know it cold.
What you can do with a finished visual query:
| Button | What it does. |
|---|---|
| View SQL > View query | Shows the T-SQL built from your steps. Edit SQL script opens it in the SQL query editor. |
| Save as view | Creates a view in a schema you pick. You check the SQL first. |
| Save as table | Saves the results as a new table with CREATE TABLE AS SELECT. |
| Download Excel file | Opens the results in Excel. |
| Visualize results | Starts a report from the results. |
A view is a saved query that people query like a table. It holds no data of its own. With several tables, only the query with Enable load turned on goes into the SQL, the view and the table.
You are an analytics engineer at Juniper Coffee. A finance analyst with no SQL skills built a visual query of weekly sales by region. Other analysts must query the same result from Power BI, and it must always show current data. What should she do?
Pick one answer.
The SQL query editor
Exam depth: know it cold.
The SQL query editor is a T-SQL text editor with IntelliSense, a feature that suggests names as you type. Select New SQL query, write, then select Run.
A select, filter and aggregate query uses these clauses, in this order:
| Clause | Job. |
|---|---|
SELECT | Columns to return. TOP (n) limits the rows. |
FROM / JOIN | Tables to read. |
WHERE | Filters rows before grouping. |
GROUP BY | One row per group, for SUM, COUNT, AVG. |
HAVING | Filters groups after grouping. |
ORDER BY | Sorts the result. |
The results grid previews up to 10,000 rows. To save a query, highlight its SELECT statement first. Then pick Save as view, Save as table, Open in Excel, Explore this data or Visualize results.
SELECT TOP (10) s.StoreName, SUM(f.Amount) AS RevenueFROM dbo.FactSales AS fJOIN dbo.DimStore AS s ON s.StoreKey = f.StoreKeyWHERE s.Region = 'North'AND f.SaleDate >= '2026-01-01' AND f.SaleDate < '2027-01-01'GROUP BY s.StoreNameHAVING SUM(f.Amount) > 50000ORDER BY Revenue DESC;
Put in order
Put the clauses in the order you write them in a T-SQL query
Drag, or focus an item and press Space, move with the arrow keys, then Space to drop.
- GROUP BY ...
- HAVING ...
- ORDER BY ...
- SELECT TOP (10) ...
- FROM ... JOIN ...
- WHERE ...
You are an analytics engineer at Juniper Coffee. This query should list the five cafés with the highest revenue, but returns a different five each run: SELECT TOP (5) StoreName, SUM(Amount) AS Revenue FROM dbo.SalesByStore GROUP BY StoreName; What should you add?
Pick one answer.
Query across items with three-part names
Exam depth: know it cold.
You can join a Warehouse table to a lakehouse table without copying either. Both must be in the same workspace.
- In the Explorer, select + Warehouses and add the other Warehouse or SQL analytics endpoint.
- Refer to its tables with a three-part name:
item.schema.table, such asJuniperLakehouse.dbo.SupplierPrices.
A two-part name, schema.table, means the item you're in. In the Visual query editor, you drag tables from the added item onto the canvas and merge them.
SELECT p.SKU, p.ProductName, sp.UnitPriceFROM dbo.DimProduct AS pJOIN JuniperLakehouse.dbo.SupplierPrices AS spON sp.SKU = p.SKU;
You are an analytics engineer at Juniper Coffee. Product data is in a Warehouse. Supplier prices are in a Lakehouse in the same workspace. You must report cost per product without copying any data. What should you do?
Pick one answer.
Try it in Fabric
Use any Warehouse with a fact table and a dimension table, such as the Warehouse sample.
- Open the Warehouse and select New visual query.
- Drag the fact table and a dimension table onto the canvas.
- On the dimension, select Merge queries as new. Pick the shared key in both tables, choose Inner, select OK.
- Expand the merged column and keep the amount column.
- Select Transform > Group by. Group by a dimension column, such as city, and Sum the amount.
- Select View SQL > View query to read the T-SQL.
- Select Save as view, pick the dbo schema, name it, check the SQL and confirm. It appears under Views in the Explorer.
Exam-style practice
You are an analytics engineer at Juniper Coffee. A store moved regions, and you must change its Region value in the DimStore table of the Warehouse. You prefer no-code tools where possible. Which tool should you use?
Pick one answer.
You are an analytics engineer at Juniper Coffee. A visual query shows a banner: the query isn't supported as a warehouse view, since it can't be fully translated to SQL. You need to save it as a view. What should you do?
Pick one answer.
Worth keeping
- No SQL skills: Visual query editor. Changing data or objects: SQL query editor.
- The Visual query editor runs read-only SELECT built from Power Query steps that fold to SQL.
- Save as view for a reusable, current query; Save as table for a copy of the results.
- WHERE filters rows, GROUP BY makes groups, HAVING filters groups, and TOP needs ORDER BY.
- Join items in the same workspace with three-part names: item.schema.table.
The loyalty app's events don't land in the Warehouse. They stream into an Eventhouse, and SQL isn't its first language. Next lesson: select, filter and aggregate with KQL.
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 (8)
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
- [110]Microsoft Learn: Query folding indicators
- [153]Microsoft Learn: Query the warehouse or SQL analytics endpoint
- [168]Microsoft Learn: Query using the visual query editor
- [169]Microsoft Learn: Query using the SQL query editor
- [170]Microsoft Learn: SELECT (Transact-SQL)
- [171]Microsoft Learn: TOP (Transact-SQL)
- [172]Microsoft Learn: What is the SQL analytics endpoint for a lakehouse?