Text.AfterDelimiter Power Query function
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 “/.”
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.
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
“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
