Text.BeforeDelimiter Function in Power Query M (6 Examples)

If you want to grab the part of a text value that comes before a certain character, like the folder name before the first slash or the username before the @ in an email, Text.BeforeDelimiter is the function you reach for.

In this article I’ll show you how it works with six quick examples.

Table of Contents

Syntax of Text.BeforeDelimiter Function

Text.BeforeDelimiter(text as nullable text, delimiter as text, optional index as any) as any

where

text (required, nullable text). The text value you want to pull a piece out of.
delimiter (required, text). The character or string to look for. The function returns everything before it.
index (optional, any). Which occurrence of the delimiter to stop at. Omit it to use the first occurrence. Give it a number for the Nth occurrence counting from the start (0-based, so 0 is the first, 1 is the second, and so on). Or give it a list of the form {relativeIndex,RelativePosition.FromStart} or {relativeIndex,RelativePosition.FromEnd} to count occurrences from the start or the end.

Returns: a text value containing the part of text that sits before the chosen delimiter. If the delimiter is not found, it returns the whole input string unchanged.

In plain terms, you hand it some text and a delimiter, and it gives you back everything to the left of that delimiter.

Example 1: Get the text before the first slash

Pull the top folder name out of a file path by stopping at the first /.

Text.BeforeDelimiter("reports/2024/q3/summary.pdf","/")

Result: reports

With no index, the function stops at the first / and returns everything before it.

Example 2: Stop at the third occurrence of a delimiter

You have a server name split by hyphens and you want everything before the last hyphen. There are three hyphens, so you stop at the third one by passing index 2 (0-based).

Text.BeforeDelimiter("app-server-prod-01","-",2)

Result: app-server-prod

The index 2 points at the third hyphen, so the function keeps the first three segments and drops the 01.

Example 3: Count occurrences from the end

Counting from the start is awkward when you only care about the last delimiter. Here you want the file name without its extension, so you stop at the last . by counting from the end.

Text.BeforeDelimiter("data.backup.2024.csv",".",{0,RelativePosition.FromEnd})

Result: data.backup.2024

The list {0,RelativePosition.FromEnd} means “the first dot counting from the end”, which is the last dot in the string.

Example 4: Pull the username out of an email address

Get the part before the @ in an email address.

Text.BeforeDelimiter("priya.sharma@example.com","@")

Result: priya.sharma

The first dot in priya.sharma is left alone because the delimiter here is @, not ..

Example 5: Get the first segment of a product code

Grab the category from a product code that uses hyphens as separators.

Text.BeforeDelimiter("WIDGET-BLUE-LARGE","-")

Result: WIDGET

With no index, it stops at the first hyphen and returns WIDGET.

Example 6: When the delimiter is not found

Here the delimiter | does not appear anywhere in the text.

Text.BeforeDelimiter("NoDelimiterHere","|")

Result: NoDelimiterHere

When the delimiter is missing, Text.BeforeDelimiter hands back the entire input string unchanged instead of an error or a blank. Keep this in mind, because its sibling Text.AfterDelimiter does the opposite in the same spot.

Things to keep in mind with Text.BeforeDelimiter

Text.BeforeDelimiter and Text.AfterDelimiter are asymmetric here. When the delimiter is missing, Text.BeforeDelimiter returns the whole string while Text.AfterDelimiter returns "" (an empty string). They look like mirror images but they don’t behave like mirror images, which trips people up.
The delimiter is case-sensitive. Looking for - is fine, but a letter delimiter like X will not match x. Match the exact case in your data.
For splitting a whole column at once, reach for Table.SplitColumn or a Splitter. Text.BeforeDelimiter is for pulling one piece out of one value. When you want to break a column into several columns, Table.SplitColumn with Splitter.SplitTextByDelimiter is the cleaner tool.

Common questions about Text.BeforeDelimiter

What is the difference between Text.BeforeDelimiter and Text.AfterDelimiter?

Text.BeforeDelimiter returns the part to the left of the delimiter, Text.AfterDelimiter returns the part to the right. The big gotcha is when the delimiter is missing: Text.BeforeDelimiter returns the whole string, while Text.AfterDelimiter returns an empty string.

How do I get the text before the LAST occurrence of a delimiter?

Pass {0,RelativePosition.FromEnd} as the index, like in Example 3. That counts from the end, so the first match it finds is the last delimiter in the string.

How do I get the text between two delimiters?

Combine the two functions. Take the text after the opening delimiter, then the text before the closing one: Text.BeforeDelimiter(Text.AfterDelimiter(value,"("),")") pulls out what sits between the parentheses.

List of All Power Query Functions

Related Power Query Functions / Articles: