SQL to Power Query M: a translation guide
Cheat sheet · By Tahir Nas · Examples run in our simulator · Published · Updated
Clause by clause
| SQL | M |
|---|---|
| SELECT a, b | Table.SelectColumns(T, {"a", "b"}) |
| WHERE x > 5 | Table.SelectRows(T, each [x] > 5) |
| ORDER BY x DESC | Table.Sort(T, {{"x", Order.Descending}}) |
| TOP 10 / LIMIT 10 | Table.FirstN(T, 10) after sorting |
| DISTINCT | Table.Distinct(T) |
| GROUP BY k + SUM(v) | Table.Group(T, {"k"}, {{"v", each List.Sum([v])}}) |
| HAVING SUM(v) > 100 | Table.SelectRows after the Group step |
| LEFT JOIN | Table.NestedJoin(…, JoinKind.LeftOuter) + expand |
| LEFT JOIN … WHERE r.key IS NULL | JoinKind.LeftAnti |
| UNION ALL | Table.Combine({A, B}) |
| CASE WHEN … END | if … then … else |
| COALESCE(a, b) | a ?? b |
A full query, side by side
SQL: SELECT Region, SUM(Amount) AS Total FROM Sales WHERE Status = 'Complete' GROUP BY Region HAVING SUM(Amount) > 20 ORDER BY Total DESC
let
Sales = #table({"Region", "Status", "Amount"}, {{"North", "Complete", 20}, {"South", "Complete", 12}, {"North", "Complete", 8}, {"West", "Complete", 40}, {"West", "Cancelled", 99}}),
Where = Table.SelectRows(Sales, each [Status] = "Complete"),
GroupBy = Table.Group(Where, {"Region"}, {{"Total", each List.Sum([Amount]), type number}}),
Having = Table.SelectRows(GroupBy, each [Total] > 20),
OrderBy = Table.Sort(Having, {{"Total", Order.Descending}})
in
OrderByResult
| Region | Total |
|---|---|
| West | 40 |
| North | 28 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
null is different
In SQL, NULL = NULL is unknown, so a WHERE clause drops it and a join never matches null keys. In M, null = null is true, and null <> "x" is true too:
#table({"Expression", "M result"}, {{"null = null", null = null}, {"null <> ""Cancelled""", null <> "Cancelled"}, {"null > 5", null > 5}})Result
| Expression | M result |
|---|---|
| null = null | true |
| null <> "Cancelled" | true |
| null > 5 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Two consequences: an exclusion filter like each [Status] <> "Cancelled" keeps null statuses (SQL's Status <> 'Cancelled' would drop them), and the simulator matches null keys to null keys in merges (compatibility page KD03, pending native verification). Filter out null keys before merging if they shouldn't match.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Overview of query evaluation and query folding in Power Query (Concept, Microsoft Learn)
- Merge queries overview (Concept, Microsoft Learn)
- Grouping or summarizing rows (Concept, Microsoft Learn)
- Table functions (Concept, Microsoft Learn)
- Table.SelectRows (Function reference, Microsoft Learn)
- Table.Group (Function reference, Microsoft Learn)
- List.Sum (Function reference, Microsoft Learn)
- Table.Sort (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.