Text.PositionOf
TextReturns the position of the first (or specified) occurrence of a substring within a text value, or -1 if not found.
Syntax
Text.PositionOf(text as text, substring as text, optional occurrence as nullable number, optional comparer as nullable function) as anyParameters
| Name | Type | Required | Description |
|---|---|---|---|
text | text | Yes | The text value to search within. |
substring | text | Yes | The substring to find. |
occurrence | number | No | Specifies which occurrence to find. Use Occurrence.First (default), Occurrence.Last, or Occurrence.All. |
comparer | function | No | A comparer function to control case sensitivity or culture. Use Comparer.OrdinalIgnoreCase for case-insensitive search. |
Return Value
any — The zero-based position of the occurrence, -1 if not found, or a list of positions when Occurrence.All is used.
Remarks
Text.PositionOf searches for substring within text and returns the zero-based character position of the match. Returns -1 if the substring is not found — not an error, so always check for -1 before using the result as an offset in Text.Middle or similar functions.
The optional occurrence parameter accepts values from the Occurrence enum:
- Occurrence.First (default) — returns the position of the first match
- Occurrence.Last — returns the position of the last match
- Occurrence.All — returns a list of all match positions
Power Query's text functions are case-sensitive by default. Always pass Comparer.OrdinalIgnoreCase as the comparer parameter when performing case-insensitive position lookups. This is especially important in Table.SelectRows predicates where source data casing may be inconsistent. For checking existence alone (without needing the position), Text.Contains is simpler and more expressive.
When you use Occurrence.All, the return type changes from number to list. If you then pass this result to another function expecting a number, add a runtime type check or use List.First to extract the position.
Examples
Example 1: Find the position of the first occurrence
Text.PositionOf("alice smith", "smith")Result | |
|---|---|
| 1 | 6 |
Example 2: Case-insensitive position lookup
Text.PositionOf("BOB JONES", "jones", Occurrence.First, Comparer.OrdinalIgnoreCase)Result | |
|---|---|
| 1 | 4 |
Example 3: Return all positions with Occurrence.All
Text.PositionOf("Widget A, Widget B, Widget C", "Widget", Occurrence.All)Result | |
|---|---|
| 1 | 0,10,20 |
Example 5: Use position to extract a dynamic suffix
let
fullName = "alice smith",
spacePos = Text.PositionOf(fullName, " ")
in
if spacePos = -1 then fullName else Text.Middle(fullName, spacePos + 1)The final output — since spacePos is not -1, extracts all text after the space using Text.Middle starting at index 6, returning the last name "smith".
Result | |
|---|---|
| 1 | smith |