Excel formulas and their Power Query equivalents
Cheat sheet · By Tahir Nas · Examples run in our simulator · Published · Updated
Translation table
| Excel | Power Query | Notes |
|---|---|---|
| VLOOKUP / XLOOKUP | Merge Queries (left outer) + expand | Returns all matches, not just the first |
| SUMIFS / COUNTIFS / AVERAGEIFS | Group By (filter first if needed) | Or List.Sum(List.Select(...)) in a custom column |
| IF / IFS | if … then … else | else is mandatory |
| IFERROR | try … otherwise | Or Replace Errors on a column |
| TRIM | Text.Trim | Doesn't collapse inner spaces |
| LEFT / RIGHT | Text.Start / Text.End | |
| MID | Text.Middle | Starts at 0, not 1 |
| CONCAT / TEXTJOIN | & / Text.Combine | & with null is null; Text.Combine skips nulls |
| TEXT(date, "yyyy-mm") | Date.ToText(d, [Format = "yyyy-MM"]) | MM is month, mm is minutes |
| ROUND | Number.Round(x, n, RoundingMode.AwayFromZero) | Default rounding is half to even |
| EOMONTH | Date.EndOfMonth | Then Date.AddMonths for offsets |
| UNIQUE / SORT / FILTER | Remove Duplicates / Sort / Filter rows | All refresh with the data |
The three classic differences, tested
#table({"Excel formula", "Excel result", "Power Query", "PQ result"}, {
{"=TRIM(""a b"")", "a b", "Text.Trim(""a b"")", Text.Trim("a b")},
{"=MID(""ABCDE"", 2, 3)", "BCD", "Text.Middle(""ABCDE"", 1, 3)", Text.Middle("ABCDE", 1, 3)},
{"=ROUND(2.5, 0)", "3", "Number.Round(2.5, 0)", Text.From(Number.Round(2.5, 0))}
})Result
| Excel formula | Excel result | Power Query | PQ result |
|---|---|---|---|
| =TRIM("a b") | a b | Text.Trim("a b") | a b |
| =MID("ABCDE", 2, 3) | BCD | Text.Middle("ABCDE", 1, 3) | BCD |
| =ROUND(2.5, 0) | 3 | Number.Round(2.5, 0) | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
XLOOKUP with an if-not-found value
let
Orders = #table({"OrderID", "SKU"}, {{"A-1", "K-01"}, {"A-2", "Z-99"}}),
Prices = #table({"SKU", "Price"}, {{"K-01", 19}, {"R-02", 6}}),
Merged = Table.ExpandTableColumn(Table.NestedJoin(Orders, {"SKU"}, Prices, {"SKU"}, "P", JoinKind.LeftOuter), "P", {"Price"}),
NotFound = Table.TransformColumns(Merged, {{"Price", each _ ?? 0, type number}})
in
NotFoundResult
| OrderID | SKU | Price |
|---|---|---|
| A-1 | K-01 | 19 |
| A-2 | Z-99 | 0 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
A merge returns every match. If the price list has two rows for K-01, both come back and the order appears twice, where XLOOKUP would quietly return the first. That's usually a data problem worth finding, so check the lookup key is unique before relying on the merge.
SUMIFS as a step
let
Sales = #table({"Region", "Status", "Amount"}, {{"North", "Complete", 20}, {"North", "Cancelled", 35}, {"South", "Complete", 12}, {"North", "Complete", 8}}),
Complete = Table.SelectRows(Sales, each [Status] = "Complete"),
SumIfs = Table.Group(Complete, {"Region"}, {{"Amount", each List.Sum([Amount]), type number}})
in
SumIfsResult
| Region | Amount |
|---|---|
| North | 28 |
| South | 12 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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.
- intermediate~9 minPro
Look up each order's customer city (kite shop)
Merge Orders with Customers on Customer and expand City, keeping every order.
- intermediate~8 minPro
Summarise orders by region (kite shop)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Parse sensor readings safely with try … otherwise (greenhouse)
Add a numeric Reading column that is null whenever Raw isn't a number.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Understanding Power Query M functions (Concept, Microsoft Learn)
- Text functions (Concept, Microsoft Learn)
- Date functions (Concept, Microsoft Learn)
- Text.Trim (Function reference, Microsoft Learn)
- Text.Middle (Function reference, Microsoft Learn)
- Number.Round (Function reference, Microsoft Learn)
- Text.From (Function reference, Microsoft Learn)
- Table.ExpandTableColumn (Function reference, Microsoft Learn)
- Table.NestedJoin (Function reference, Microsoft Learn)
- Table.TransformColumns (Function reference, Microsoft Learn)
- Table.SelectRows (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.