Power Query text functions cheat sheet (tested)
Cheat sheet · By Tahir Nas · Examples run in our simulator · Published · Updated
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
| Expression | Result |
|---|---|
| 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.OrdinalIgnoreCasewhere 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.
- beginner~6 minPro
Split email addresses into user and domain (logistics)
Add the part before @ as User and the part after @ as Domain.
- intermediate~6 minPro
Build fixed-width badge numbers (logistics)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~7 minPro
Flag tickets that mention billing, in any case (telecom)
Add an Is Billing flag when Topic contains "bill" regardless of capitalisation.
Related pages
Lessons
More resources
Sources
Microsoft's official documentation for the features and functions used on this page.
- Text functions (Concept, Microsoft Learn)
- Split columns by delimiter (Concept, Microsoft Learn)
- Text.Trim (Function reference, Microsoft Learn)
- Text.Upper (Function reference, Microsoft Learn)
- Text.Proper (Function reference, Microsoft Learn)
- Text.Length (Function reference, Microsoft Learn)
- Text.From (Function reference, Microsoft Learn)
- Text.Start (Function reference, Microsoft Learn)
- Text.End (Function reference, Microsoft Learn)
- Text.Middle (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.