Forum Discussion

Kebas_Leech's avatar
Kebas_Leech
Helper I
3 years ago
Solved

M Language removing text based on mulitple columns

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 WoodMadeOf WoodHouse

 

Any Help? Cant make it work for two columns 

Thanks

Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    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

     

2 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    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

     

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

    Add new column

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

    Stéphane