M Language removing text based on mulitple columns

[Kebas_Leech]

Hello,

Based on a condition that a column [Name] contains text "Jonh" i need it to do the following: 

Check which words of column [Info] and [Extrainfo] are contained in column [Prod] and remove them.

[Prod]	[Info]	[Extrainfo]	Result
House Made Of Wood	Made	Of Wood	House

Any Help? Cant make it work for two columns 

Thanks

[slorin]

Hi

Add new column

if [Name]="Jonh" then Replacer.ReplaceText(Replacer.ReplaceText([Prod],[Extrainfo],""),[Info],"") else [Prod]

Stéphane 

[PhilipTreacy · Accepted solution]

Hi @Kebas_Leech 

Download example PBIX file

This code will do it.  I assume the name is meant to be John, rather than Jonh.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfLILy1OVfBNTElV8E9TCM/PTwEKgrhACiYQGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Prod = _t, Info = _t, Extrainfo = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result", each if [Name] = "John" then Text.Combine(List.RemoveItems(Text.Split([Prod], " "), Text.Split([Info] & " " & [Extrainfo], " ")), " ") else "0")
in
    #"Added Custom"

Regards

Phil