Power QueryExercises

SQL to Power Query M: a translation guide

Cheat sheet · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Clause by clause

SQLM
SELECT a, bTable.SelectColumns(T, {"a", "b"})
WHERE x > 5Table.SelectRows(T, each [x] > 5)
ORDER BY x DESCTable.Sort(T, {{"x", Order.Descending}})
TOP 10 / LIMIT 10Table.FirstN(T, 10) after sorting
DISTINCTTable.Distinct(T)
GROUP BY k + SUM(v)Table.Group(T, {"k"}, {{"v", each List.Sum([v])}})
HAVING SUM(v) > 100Table.SelectRows after the Group step
LEFT JOINTable.NestedJoin(…, JoinKind.LeftOuter) + expand
LEFT JOIN … WHERE r.key IS NULLJoinKind.LeftAnti
UNION ALLTable.Combine({A, B})
CASE WHEN … ENDif … 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
    OrderBy

Result

RegionTotal
West40
North28

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

ExpressionM result
null = nulltrue
null <> "Cancelled"true
null > 5null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Ordered comparisons with null are null (and filters drop them, as in SQL); equality isn't.

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.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

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.