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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @allora,

 

How about this?

tackytechtom_2-1678326162412.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 

View solution in original post

1 REPLY 1
tackytechtom
Super User
Super User

Hi @allora,

 

How about this?

tackytechtom_2-1678326162412.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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors
Top Kudoed Authors