Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
allora
Frequent Visitor

How to remove text before a delimiter only for rows with the delimiter

I have a column like below:

 

ID
John: 0001
0002
0004
Tim: 0005

 

I would like remove the text before the delimiter ":" only for the rows that have the delimiter. I am having a bit of trouble figuring this out. The final output should be like below:

 

ID
0001
0002
0004
0005
1 REPLY 1
tackytechtom
Super User
Super User

Hi @allora,

 

How about this?

 

tackytechtom_0-1678325792190.png

 

Here the code for the calculated column:

if Text.Contains ( [ID], ":" ) then  Text.TrimStart ( Text.AfterDelimiter([ID], ":") ) else [ID]


And here the complete M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyLNSMDAwMFSK1YlWAjKMYAwTMCMkMxcsb6oUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains ( [ID], ":" ) then  Text.TrimStart ( Text.AfterDelimiter([ID], ":") ) else [ID])
in
    #"Added Custom"

 

I understood your query that you actually don't want a new column but instead you'd like to replace the values. I recommend you this blog post here:

 

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#7_Replace_values_in_column

 

It explains how you can use the code from the new calculated column in order to replace the values (instead of adding a new one)

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors