Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Remove certain words from text column using column2

remove certain strings from the column

InputInputoutput
Hi I am having issue with this productamHi I having issue with this product
Error product live Monday dumm coolcoolError product live Monday dumm 
defective product hello dummydudedefective product hello dummy
issue report  issue report 
I am having trouble dude I  having trouble 
lovely lady  lovely lady 
I am cool dude I 
Need Help
Show and Tell
Tips and Tricks
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    You can leverage the List.ReplaceMatchingItems function with Table.AddColumn.

     

    Words to Remove

    let

//Read in original data
    Source = Excel.CurrentWorkbook(){[Name="InputData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),

//Read in list of words to remove
    Source2 = Excel.CurrentWorkbook(){[Name="removeWords"]}[Content],
    RemoveWords = Source2[Input],

//Add column with the words to remove replace by null
    #"Remove Words" = 
        Table.AddColumn(#"Changed Type", "Output", 
            each Text.Combine(
                    List.ReplaceMatchingItems(
                        Text.Split([Input]," "), 
                        List.Transform(RemoveWords, each {_, null})),
                 " "))
in
  #"Remove Words"

     

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    You can leverage the List.ReplaceMatchingItems function with Table.AddColumn.

     

    Words to Remove

    let

//Read in original data
    Source = Excel.CurrentWorkbook(){[Name="InputData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),

//Read in list of words to remove
    Source2 = Excel.CurrentWorkbook(){[Name="removeWords"]}[Content],
    RemoveWords = Source2[Input],

//Add column with the words to remove replace by null
    #"Remove Words" = 
        Table.AddColumn(#"Changed Type", "Output", 
            each Text.Combine(
                    List.ReplaceMatchingItems(
                        Text.Split([Input]," "), 
                        List.Transform(RemoveWords, each {_, null})),
                 " "))
in
  #"Remove Words"

     

     

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    4 years ago

    Hi Anonymous ,

     

    Below is a different approach to ronrsnfld.:

     

    Outcome:

     

    Code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7RCoMwDAXQX7n02d8YbA/7AvGhM5ktpEZi6+jfTzsE2VPg5lySvnf3iAd8QvBbnCfEdS2MT8wBOcQViymVMbvO+eSGrnc3M7UzhsSN8dSZfAWVlDCqyo7bODjxm8d8qLMSWEQbrjukQtzg77DxopaxL9DS62vZtLyE0SonEN1YKsRT/WsdL1zs8AU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t, Remove = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}, {"Remove", type text}}),

    //Remove certain string
    #"Added Custom" = Table.AddColumn(
                        #"Changed Type", 
                        "Output", 
                        each 
                            if [Remove]=" " 
                            then [Input] 
                            else Text.Replace(
                                    Text.Replace(
                                        [Input],
                                        [Remove],
                                        "")
                                    , 
                                    "  ", 
                                    " "
                                )
                    )
in
    #"Added Custom"

     

    Regards

    KT