Replace flexible text

[rgu101]

Hello,

I'm creating a comments dashboard from survey data and I need to remove all names, phone numbers, and emails but I'm unsure on how to mass remove these variables from the text without manually filtering for key words such as "my name is" or "@aol.com" etc. It's thousands of rows of comments that is obviously not reasonable to manually sift through.

Is there any way to do this on PBI? For example, a command to ReplaceValue( #"Step", "My name is [string 1] [string 2]", "(REDACTED)") where [string 1] and [string 2] are any strings separated by a space (such that it assumes the 2 strings after "My name is" are first & last names).

Thank you in advance.

[marcelsmaglhaes]

Hey @rgu101 

You should try something like that:
1. Check if Column Contains Item from List in Power Query - Create Text.ContainsAny! - YouTube
2. Solved: Bulk replace text values in single step - Power Platform Community (microsoft.com)

If this post helps, please mark as solved.

Regards,
Marcel

[rgu101]

Thanks for the resources, but I'm unsure how the potential solutions you've provided would help me solve the issue. Given the resources, there are two problems that prevent me from following either solution.

1. Some comments may or may not have names, and if they do it's part of the comment text

2. I don't know the names that are in the comments so I'm trying to remove key phrases like "My name is" in addition to the 2 words that follow the key phrase.

The major issue is the 2nd problem because the 2 words that follow the key phrase are not static and assumed to by first name last name

[marcelsmaglhaes · Accepted solution]

Hey @rgu101 ,

If you have a pattern like "My name is [text1] [text2]", you can remove that string. The problem is that if you have something like "My name is Josh and I love cars", the extraction will consider removing "My name is Josh and" because the default is to remove the next 2 words after My name is. 

And like you said, you're going to assume that the next two words are going to be first and last name. If you don't set a standard, it's kind of impossible to get the job done.

You should start with something like that.

let
RemoveNameStep = Table.TransformColumns(
YourPreviousStep,
{"Column_Name", each
let
text = Text.From(_),
nameIsPosition = Text.PositionOf(text, "My name is"),
result =
if nameIsPosition >= 0 then
let
textWithoutName = Text.Start(text, nameIsPosition)
in
textWithoutName
else text
in
result
}
)

Please note that this code is a partial solution and may need further work to completely remove the desired data. The structure of Power Query can make text manipulation complex in certain cases. 
Regards,
Marcel

[rgu101]

Thank you so much for the starting point. I was able to figure a temporary method to mass remove names with the assumption of first name and last name. Obviously isn't perfect but the comments are still comprehendable even if one word is missing. I've pasted my code below.

= Table.TransformColumns(
#"Renamed Columns2",
{"test", each
let text = Text.From(_),
nameIsPosition = Text.PositionOf(text, "My name is "),
result = 
if nameIsPosition >= 0 then
let
spacePosition1 = Text.PositionOf(Text.Range(text,nameIsPosition +11), " ")+1,
spacePosition2 = Text.PositionOf(Text.Range(text,nameIsPosition+ 11 + spacePosition1), " ")+1
in Text.RemoveRange(text, nameIsPosition, 11+spacePosition1+spacePosition2)
else text in result
})