Text.BeforeDelimiter - Text Function

Text.BeforeDelimiter is a Power Query M function that extracts the portion of text before a specified delimiter. The function returns the extracted text, with optional parameters to specify which occurrence of the delimiter to consider and whether to index from the start or end of the input.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Text		This is the base text value from which you will extract.
Delimiter		Indicates the delimiter to search for.
Index	optional	Indicates which delimiter to consider. You can either: 1. provide an index number. This instructs the function to find that nth occurrence of the delimiter. 2. provide both an index number and a RelativePosition Type. This instructs whether indexing should be done from start or end of the input. You can choose from RelativePosition.FromStart of RelativePosition.FromEnd. Example argument: { 1, RelativePosition.FromStart }

Description

Returns the portion of text before the specified delimiter. An optional numeric index indicates which occurrence of the delimiter should be considered. An optional list index indicates which occurrence of the delimiter should be considered, as well as whether indexing should be done from the start or end of the input.

Examples

Let’s look at some useful examples for the Text.BeforeDelimiter function.

Extracting All Before the First Delimiter

To get the portion of “11-22-33-44” before the first hyphen, use the following formula. Both examples will return “11”. This is useful when you need the initial segment of a string up to the first occurrence of a specified delimiter.

Text.BeforeDelimiter( "11-22-33-44", "-" )    // Returns "11"
Text.BeforeDelimiter( "11-22-33-44", "-", 0 ) // Returns "11"

Extracting All Before the Second Delimiter

To get the portion of “11-22-33-44” before the second hyphen from the start, use one of the following formulas. Both examples will return “11-22”. This can be helpful when you need the text up to the second delimiter in a string.

Text.BeforeDelimiter( "11-22-33-44", "-", 1 )                                 // Returns "11-22"
Text.BeforeDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromStart } ) // Returns "11-22"

Using RelativePosition.Type for Explicit Extraction

You can be more explicit by using a RelativePosition.Type to specify the delimiter’s position. This allows you to control which delimiter to consider, especially useful in complex requirements.

Before the Second Hyphen from the Start

To retrieve the portion before the second hyphen from the start, use:

// Return the part before the second hyphen from the start
// Returns:  "11-22"
Text.BeforeDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromStart } )

Before the Second Hyphen from the End

To retrieve the portion before the second hyphen from the end, use:

// Return the part before the second hyphen from the end
// Returns:  "11-22"
Text.BeforeDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromEnd } )

Before the First Hyphen from the End

To retrieve the portion before the first hyphen from the end, you can use:

// Return the part before the first hyphen from the end
// Returns:  "11-22-33"
Text.BeforeDelimiter( "11-22-33-44", "-", { 0, RelativePosition.FromEnd } )

Summary of Possible Options

A quick glance for possible options then gives you:

= Text.BeforeDelimiter( "11-22-33-44", "-" )                                    // Returns "11"
= Text.BeforeDelimiter( "11-22-33-44", "-", { 0, RelativePosition.FromStart } ) // Returns "11"
= Text.BeforeDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromStart } ) // Returns "11-22"
= Text.BeforeDelimiter( "11-22-33-44", "-", { 2, RelativePosition.FromStart } ) // Returns "11-22-33"
= Text.BeforeDelimiter( "11-22-33-44", "-", { 0, RelativePosition.FromEnd } )   // Returns "11-22-33"
= Text.BeforeDelimiter( "11-22-33-44", "-", { 1, RelativePosition.FromEnd } )   // Returns "11-22"
= Text.BeforeDelimiter( "11-22-33-44", "-", { 2, RelativePosition.FromEnd } )   // Returns "11"

Learn more about Text.BeforeDelimiter in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.BeforeDelimiter are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-beforedelimiter