Forum Discussion
Replace flexible text
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
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
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
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,
MarcelThank 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 })