extract text after delimiter using a delimiter column

[Anonymous]

I have a [target] column and a [delimiter] column.  The [delimiter] column has values that I want to use as delimiter for the [target] column's values.  How can I extract text after delimiter (the [delimiter] column's values) and not create another column?  Alternatively, I can also find the [delimiter]'s value within the [target] column and replace with "" (empty string).  Thanks!

[Vijay_A_Verma · Accepted solution]

If you want result in another column and 

= Text.AfterDelimiter([target],[delimiter])

For in column replacement, insert following statement i.e. not create another column

= Table.ReplaceValue(Source,each [target],each Text.AfterDelimiter([target],[delimiter]),Replacer.ReplaceValue,{"target"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSqxJUtJRqlGK1YlWSk7xzNLJzskFCugoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [target = _t, delimiter = _t]),
    Custom1 = Table.ReplaceValue(Source,each [target],each Text.AfterDelimiter([target],[delimiter]),Replacer.ReplaceValue,{"target"})
in
    Custom1

[Nathaniel_C]

Hi @Anonymous ,
First picture see first 2 columns

Second picture see splitting at the "a" (for example and if there is no "a" then it will add the second col to the end.)

Third picture combine the three columns using Text.combine

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,
BTW, and you may know this already, but after you have done your calcs in Power Query, you can remove all those intermediate columns that you used to create your "result" column.

Nathaniel

[Nathaniel_C]

Hi @Anonymous ,
Select your column, select the transform tab, select the Extract button. See pictures below

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

I know how to extract "text after delimiter" by manually inputting text into the "delimiter" field, i.e. Text.AfterDelimiter(_, "delimiter").  Please reference my question about using a [delimiter] column.

[Nathaniel_C]

Hi @Anonymous , 
I was responding to this part of your question..."How can I extract text after delimiter and not create another column?"

Nathaniel