Power QueryExercises

Excel formulas and their Power Query equivalents

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

Jump to the practice tasks

Translation table

ExcelPower QueryNotes
VLOOKUP / XLOOKUPMerge Queries (left outer) + expandReturns all matches, not just the first
SUMIFS / COUNTIFS / AVERAGEIFSGroup By (filter first if needed)Or List.Sum(List.Select(...)) in a custom column
IF / IFSif … then … elseelse is mandatory
IFERRORtry … otherwiseOr Replace Errors on a column
TRIMText.TrimDoesn't collapse inner spaces
LEFT / RIGHTText.Start / Text.End
MIDText.MiddleStarts 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
ROUNDNumber.Round(x, n, RoundingMode.AwayFromZero)Default rounding is half to even
EOMONTHDate.EndOfMonthThen Date.AddMonths for offsets
UNIQUE / SORT / FILTERRemove Duplicates / Sort / Filter rowsAll 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 formulaExcel resultPower QueryPQ result
=TRIM("a b")a bText.Trim("a b")a b
=MID("ABCDE", 2, 3)BCDText.Middle("ABCDE", 1, 3)BCD
=ROUND(2.5, 0)3Number.Round(2.5, 0)2

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

The Excel results are what Excel's worksheet functions return; the PQ column is run by the simulator.

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
    NotFound

Result

OrderIDSKUPrice
A-1K-0119
A-2Z-990

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

Like XLOOKUP's if_not_found argument. Think twice before defaulting a price to 0; null may be more honest.

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
    SumIfs

Result

RegionAmount
North28
South12

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

=SUMIFS(Amount, Region, "North", Status, "Complete") for every region at once.

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.