Downloads
Power Query MTextIntermediate

Text.PositionOf

Returns the zero-based position where a piece of text first (or last) appears in a value.

Syntax
Text.PositionOf(text as nullable text, substring as text, optional occurrence as nullable number, optional comparer as nullable function) as number

Returns: The position as a number, or -1 when the text is not found.

What it does

Text.PositionOf tells you where a piece of text sits in a value. It is the M way to find a separator before you cut a value with Text.Start, Text.Middle or Text.End. Positions start at 0, and a result of -1 means the text was not found, so the result must be checked before it is used.

Syntax and arguments

Example

A subledger reference has a sub-account after its last dash. The position of the last dash is found so the sub-account can be cut out with Text.End.

let
    Reference = "1000-CASH-MAIN",
    LastDash = Text.PositionOf(Reference, "-", Occurrence.Last),
    SubAccount = Text.End(Reference, Text.Length(Reference) - LastDash - 1)
in
    {LastDash, SubAccount}

LastDash is 9, the position of the second dash. SubAccount is "MAIN", the text after it. The length of the end part is the total length minus the position minus one.

Example: a second, different use

Find the position of the first "@" in an email address to check that the address has a domain part before sending a statement.

Things to watch

In business terms

Reference codes with a separator in a known place are common in ledgers and bank files. Locating the separator and then cutting the value is how a team extracts a sub-account, a batch or a suffix without fixed positions.

Related functions

All Power Query M functions · View as Markdown · M basics