Text.AfterDelimiter Power Query function

Text.AfterDelimiter Power Query function

ByPower BI

Suppose you’re looking for a way to split text by a specific delimiter in Microsoft Power Query, the Text.AfterDelimiter function is what you need. It helps us to extract everything after a particular delimiter in a text value. In this article, we’ll show you how to use the Text.AfterDelimiter function with some examples.

Syntax

Text.AfterDelimiter(text as nullable text, delimiter as text, optional index as any) as any
  • It takes three parameters
  • The first two parameters are compulsory, and the third parameter is optional.
  • The first parameter is the string from which you want to extract the required substring.
  • The second parameter is the delimiter.
  • The third parameter is the delimiter position that you must consider to extract the substring.

Power Query extract text from string using Text.AfterDelimiter

We have the following data, and we want to extract the string after the delimiter “/.”

Dates dataset

We have a column containing dates, and we want to fetch the data after “/” using the following formula.

  • Go to the Power query editor.
  • Select add a column and click on the custom column
  • Write the following formula
Text.AfterDelimiter([Date],"/")

You will get the following result.

Text.AfterDelimiter power query function

As you can see, it extracts the data after the first delimiter “/,” but if we want to extract the data after the second occurrence of the delimiter, then we have to write the following formula.

Text.AfterDelimiter([Date],"/",1)

In the above formula, we have used the third parameter, whose value is 1, which is the second position index.

Now we got the year 2022 after the last delimiter.

Power query extract text using GUI

You can also extract the data after the delimiter without writing any function. You can use the extract function from the power query GUI, as shown below.

  • Go to Power query editor
  • Click add column
  • click Extract and select “Text after delimiter” option
extract data power query

“Text After Delimiter” windows will appear as you can see below.

  • Under delimiter, we have to put any delimiter
  • Under “Scan for the delimiter”, there are two options available. First one is “From end of the input” and the second is “From the start of the input”
  • Under “Number of delimiters to skip”, you have to write from zero to onwards according to the occurrence of the delimiter.

You can play with numbers 2 and 3 to extract the desired substring.

Drawbacks of using the Text.AfterDelimiter function

There are some potential drawbacks to using the Text.AfterDelimiter function as well. One issue is that it is not always accurate. For example, suppose multiple instances of the delimiter character exist in the text data, the Text.AfterDelimiter function will only return the text after the first instance of the delimiter.

To learn more, please visit Learn DAX

Similar Posts

How table.replacevalue power query function works?

How table.replacevalue power query function works?

ByPower BI

Table.replacevalue power query function helps us to replace the old value with the new value of our choice. The syntax of Table.replacevalue function is It is a pretty useful function during the data cleaning process for any Power BI project. In this post, we will see different flavors of Table.replacevalue function in Power query. Are…

Text.BetweenDelimiters

What is Text.BetweenDelimiters and how do we use it?

ByPower BI

Text.BetweenDelimiters is a Power Query function in Power BI that returns the portion of text between the specified startDelimiter and endDelimiter. An optional numeric startIndex indicates which occurrence of the startDelimiter should be considered. An optional list startIndex indicates which occurrence of the startDelimiter should be considered, as well as whether indexing should be done from the start or end of the input. The endIndex is similar, except that indexing…

Text.BeforeDelimiter

Text.BeforeDelimiter Power Query function

ByPower BI

Text.beforedelimiter is a power query function that returns the substring before a specific delimiter. It helps us to clean the data before importing it into Power BI. Syntax Text.BeforeDelimiter takes three parameters. The first two parameters are compulsory, and the third parameter is optional. Example Let’s take the following example to extract the substring using…

Text.PadStart in Power BI

How to use Text.PadStart in Power BI?

ByPower BI

In this article we will what Text.PadStart is, the syntax and show you how to use it. Text.PadStart is a Power Query function that returns a text value padded to length count by inserting spaces at the start of the text value text. An optional character character can be used to specify the character used for padding. The default pad character is…