Delete Words from Row

[datadmin-austin]

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!

[mangaus1111 · Accepted solution]

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.

[datadmin-austin]

Hello @mangaus1111 ,

Thank you! I was able to do this - only alteration was adding a space before and after the comma.

[mangaus1111]

Hi @datadmin-austin ,

in power query or DAX?

[datadmin-austin]

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.