Forum Discussion
Delete Words from Row
Hello All,
I am not sure if this is possible. I have rows of data with user names seperated by commas. Each row has two of the same user names "user1" and "user2" with a 3rd name that is typically different. The order of these users is different for each row.
Is it possible to do below and exclude names based on text since the position is not cosistent?
Current
User Column
user1, user2, user3
user2, user5, user1
user7, user1, user2
Output
User Column
user3
user5
user7
I appreciate any help on this issue!
In PQ you can use this code
#"Changed Type" = Table.TransformColumnTypes(Facts44_Table,{{"User", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","user1","",Replacer.ReplaceText,{"User"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","user2","",Replacer.ReplaceText,{"User"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",",","",Replacer.ReplaceText,{"User"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value2",{{"User", Text.Trim, type text}})
in
#"Trimmed Text"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
In PQ you can use this code
#"Changed Type" = Table.TransformColumnTypes(Facts44_Table,{{"User", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","user1","",Replacer.ReplaceText,{"User"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","user2","",Replacer.ReplaceText,{"User"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",",","",Replacer.ReplaceText,{"User"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value2",{{"User", Text.Trim, type text}})
in
#"Trimmed Text"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello mangaus1111 ,
Thank you! I was able to do this - only alteration was adding a space before and after the comma.
Hello mangaus1111 ,
Either one works for me. This data comes from a SharePoint so many power query is better but I am open to suggestion.
