cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dhruvgulati
Helper I
Helper I

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 
1 ACCEPTED SOLUTION
ronrsnfld
Memorable Member
Memorable Member

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

 

Words to Remove

ronrsnfld_1-1660440693654.png

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"

ronrsnfld_0-1660440657443.png

 

 

View solution in original post

2 REPLIES 2
KT_Bsmart2gethe
Super User
Super User

Hi @dhruvgulati ,

 

Below is a different approach to @ronrsnfld.:

 

Outcome:

KT_Bsmart2gethe_0-1660480096657.png

 

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

ronrsnfld
Memorable Member
Memorable Member

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

 

Words to Remove

ronrsnfld_1-1660440693654.png

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"

ronrsnfld_0-1660440657443.png

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors