If you want to check whether a piece of text begins with a specific prefix, or filter a table down to the rows whose values start with a certain code, Text.StartsWith is the function to use.
It returns true or false, and in this article I’ll walk you through how it works with a few practical examples.
Syntax of Text.StartsWith Function
Text.StartsWith(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical
where
text(required, nullable text). The text value you want to check.substring(required, text). The prefix to test for at the start oftext.comparer(optional, nullable function). Controls how the two values are compared, for example case-insensitive matching. Leave it out for the default case-sensitive match.
Returns: a logical value, either true or false. If text is null, it returns null.
In plain terms, you give it some text and a prefix, and it tells you whether the text begins with that prefix.
Example 1: Check if a code starts with a prefix
Test whether an invoice code begins with INV.
Text.StartsWith("INV-2045","INV")
Result: true
The text starts with INV, so the function returns true.
Example 2: Matching is case-sensitive by default
Here the same code is checked against a lowercase inv.
Text.StartsWith("INV-2045","inv")
Result: false
The default comparer is Comparer.Ordinal, which is case-sensitive. So inv does not match the uppercase INV.
Example 3: Ignore case with a comparer
To match regardless of case, pass Comparer.OrdinalIgnoreCase as the third argument.
Text.StartsWith("INV-2045","inv",Comparer.OrdinalIgnoreCase)
Result: true
Now the casing of the prefix no longer matters, so inv matches INV-2045.
Example 4: Filter rows by their prefix
Filtering rows is the most common reason you’ll reach for Text.StartsWith.
Say you have an Orders query with a Code and an Amount column, and you only want the invoice rows.
Here is the starting data:
| Code | Amount |
|---|---|
| INV-2045 | 120 |
| CRN-9001 | 40 |
| INV-2046 | 80 |
| REF-3300 | 55 |
| INV-2050 | 210 |
Wrap Text.StartsWith inside Table.SelectRows to keep only the rows whose Code starts with INV:
let
Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
Result = Table.SelectRows(Source, each Text.StartsWith([Code],"INV"))
in
Result
This keeps every row where Code begins with INV.
The result has only the matching rows:
| Code | Amount |
|---|---|
| INV-2045 | 120 |
| INV-2046 | 80 |
| INV-2050 | 210 |
The credit-note and refund rows are dropped because their codes don’t start with INV.
Example 5: A null text returns null
If the text argument is null, the function returns null rather than false.
Text.StartsWith(null,"INV")
Result: null
This matters when a column can hold blanks, because a null slipping into a filter can drop rows you expected to keep.
Guard a blank-prone column by coalescing to an empty string first:
Text.StartsWith([Col] ?? "","INV")
Now a null becomes "", which cleanly returns false.
Things to keep in mind with Text.StartsWith
- Both inputs must be text. A number throws
Expression.Error: We cannot convert the value 42 to type Text.Convert it first withText.From. - It matches literally, no wildcards.
"A*"looks for the charactersA*at the start, it isn’t a pattern. - The
comparercan do more than ignore case. BeyondComparer.OrdinalIgnoreCase, you can passComparer.FromCulturefor culture-aware matching when accented or locale-specific text needs to compare a certain way.
Common questions about Text.StartsWith
What is the difference between Text.StartsWith, Text.EndsWith, and Text.Contains?
Text.StartsWith checks the beginning of the text, Text.EndsWith checks the end, and Text.Contains checks anywhere inside it.
Can I check for several prefixes at once?
Yes. To do this, combine the checks with or, for example Text.StartsWith([Code],"INV") or Text.StartsWith([Code],"CRN"). For a longer list, use List.AnyTrue(List.Transform(prefixes, each Text.StartsWith([Code],_))).
List of All Power Query Functions
Related Power Query Functions / Articles:
