Forum Discussion
extract text after delimiter using a delimiter column
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!
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
6 Replies
- Nathaniel_C
Community Champion
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- 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
- Nathaniel_C
Hi Anonymous ,
First picture see first 2 columnsSecond 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
- Vijay_A_Verma
Most Valuable Professional
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
