cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
atpbi10
Frequent Visitor

Remove certain string from text in the column

Hey!

How can I transform this column which contains certain string that starts with certain character which is in my case "PL"

 

Name
Jay Chua PL231221
John Cena
Arnold Black PL212233
Jack Don

 

To the one below? So essentially I need to remove all occurences of the string in the values based on condition.

 

Name
Jay Chua
John Cena
Arnold Black
Jack Don

 

Here is my attempt, where I created custom function but the code does not work as intended at all:

 

 

let
    Source = Excel.Workbook(File.Contents("***"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"WrongName", "Name"}}),
    #"Invoked Custom Function" = Table.TransformColumns(#"Renamed Columns", {"Name",  each if Text.Start(Text.From(_),2)="PL" then "" else _})
in
    #"Invoked Custom Function"

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

You can add a custom column with this formula

 

= Text.Combine(List.Select(Text.Split([Name], " "), each not(Text.Contains(_, "PL"))), " ")

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Rocco_sprmnt21
Super User II
Super User II

If the string to be deleted is at the end of the text (or better,if the text to keep is before the PLxyz pattern ), as in your examples, you can create a custom column in this way.

You could eventually change "PL" with " PL" to trim the trailing space

 

immagine.png

mahoneypat
Super User IV
Super User IV

You can add a custom column with this formula

 

= Text.Combine(List.Select(Text.Split([Name], " "), each not(Text.Contains(_, "PL"))), " ")

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hey thanks for the solution, works properly. Just wanted to make sure if this is case sensitive? Like for the table in the example if I have guy named "Max Planck", he wont have his surname removed?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors