Power QueryExercises

Power Query text functions cheat sheet (tested)

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

Jump to the practice tasks

Every row below runs on the same input text, " Kite-Shop Leeds " (with a leading and trailing space), unless the expression says otherwise. Positions count from 0.

let
    s = " Kite-Shop Leeds ",
    Rows = {
        {"Text.Trim(s)", Text.Trim(s)},
        {"Text.Upper(s)", Text.Upper(s)},
        {"Text.Proper(""kITE shop"")", Text.Proper("kITE shop")},
        {"Text.Length(s)", Text.From(Text.Length(s))},
        {"Text.Start(Text.Trim(s), 4)", Text.Start(Text.Trim(s), 4)},
        {"Text.End(Text.Trim(s), 5)", Text.End(Text.Trim(s), 5)},
        {"Text.Middle(Text.Trim(s), 5, 4)", Text.Middle(Text.Trim(s), 5, 4)},
        {"Text.BeforeDelimiter(s, ""-"")", Text.BeforeDelimiter(s, "-")},
        {"Text.AfterDelimiter(s, "" "", 1)", Text.AfterDelimiter(s, " ", 1)},
        {"Text.Replace(s, ""Shop"", ""Store"")", Text.Replace(s, "Shop", "Store")},
        {"Text.Contains(s, ""shop"")", Text.From(Text.Contains(s, "shop"))},
        {"Text.Contains(s, ""shop"", Comparer.OrdinalIgnoreCase)", Text.From(Text.Contains(s, "shop", Comparer.OrdinalIgnoreCase))},
        {"Text.StartsWith(Text.Trim(s), ""Kite"")", Text.From(Text.StartsWith(Text.Trim(s), "Kite"))},
        {"Text.PadStart(""42"", 5, ""0"")", Text.PadStart("42", 5, "0")},
        {"Text.Remove(s, {"" "", ""-""})", Text.Remove(s, {" ", "-"})},
        {"Text.Select(""A1-B22"", {""0""..""9""})", Text.Select("A1-B22", {"0".."9"})},
        {"Text.PositionOf(s, ""Leeds"")", Text.From(Text.PositionOf(s, "Leeds"))}
    }
in
    #table({"Expression", "Result"}, Rows)

Result

ExpressionResult
Text.Trim(s)Kite-Shop Leeds
Text.Upper(s) KITE-SHOP LEEDS
Text.Proper("kITE shop")Kite Shop
Text.Length(s)17
Text.Start(Text.Trim(s), 4)Kite
Text.End(Text.Trim(s), 5)Leeds
Text.Middle(Text.Trim(s), 5, 4)Shop
Text.BeforeDelimiter(s, "-") Kite
Text.AfterDelimiter(s, " ", 1)Leeds
Text.Replace(s, "Shop", "Store") Kite-Store Leeds
Text.Contains(s, "shop")false
Text.Contains(s, "shop", Comparer.OrdinalIgnoreCase)true
Text.StartsWith(Text.Trim(s), "Kite")true
Text.PadStart("42", 5, "0")00042
Text.Remove(s, {" ", "-"})KiteShopLeeds
Text.Select("A1-B22", {"0".."9"})122
Text.PositionOf(s, "Leeds")11

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

Text.AfterDelimiter(s, " ", 1) skips the first space (index 0, the leading one) and splits at the second.

Things that surprise people

  • Text.Trim removes only leading and trailing whitespace; inner double spaces stay.
  • Text.Contains, Text.StartsWith and Replace Values are case-sensitive by default; pass Comparer.OrdinalIgnoreCase where supported.
  • Text.Middle and Text.Range count from 0. Text.Range errors if the range runs past the end; Text.Middle returns what's there.
  • Text.PositionOf returns -1 when the text isn't found, not null.
  • Text.Proper lowercases the rest of each word: McDonald becomes Mcdonald.

Most of these functions return null when given null, rather than raising an error, so a column with gaps keeps its gaps. Text.Length(null) is null, not 0; wrap it as Text.Length(_ ?? "") if the report needs a number for every row.

When a function isn't in the simulator's supported subset, the simulator says so instead of approximating it. The compatibility page lists every supported text function.

Positions are where most off-by-one mistakes happen: Text.Start and Text.End take a count, Text.Middle and Text.Range take a zero-based start and a count. Write a two-row test table with values of different lengths and check the result before applying a position-based step to real data.

Column versions

To apply any of these to a column, use Table.TransformColumns (replace values) or Table.AddColumn (new column), for example Table.TransformColumns(T, {{"Name", Text.Trim, type text}}). Functions with extra arguments need each: {{"Code", each Text.PadStart(_, 5, "0"), type text}}.

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

Function reference

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.