Text.BeforeDelimiter

Updated on

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
ArgumentAttributeDescription
TextThis is the base text value from which you will extract.
DelimiterIndicates the delimiter to search for.
IndexoptionalIndicates 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

Get the portion of “11-22-33-44” before the ( first ) hyphen.

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

Get the portion of “11-22-33-44” before the second hyphen from the start.

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

You can also be more explicit by using a RelativePosition.Type.

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

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

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

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:

Other functions related to Text.BeforeDelimiter are:

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