SOLVED

Text.Remove a specific word in Power Query

Brass Contributor

Am I able to use Text.Remove in power Query to remove specific words from a column such as "BLACK" :WHITE" "RED". if so what is the formula? I have been trying with a custom column and the using Text.Remove and Text.Select

 

Thank you Eqa

9 Replies

@Eqa33 

Text.Replace could do what you are looking for.

Text.Replace - PowerQuery M | Microsoft Learn

Thanks for this. Yes it works with one old and one new word, but when I want to enter more than one old word it gives me an error. I am obviously doing something wrong. I have them in a list form with curly brackets and tried without curly brackets.

@Eqa33 

Replacing of few words at once, for example Black and Red here

image.png

could be done as

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    Before = {"Black", "Red"},
    After = {"", ""},
    ListReplacmentsTexts = List.Zip(
        {
            Before, After
        }
    ),

    ReplaceTexts = Table.TransformColumns(
        Source,
        {
            {"Product",
            each List.Accumulate(ListReplacmentsTexts,_,(x,y)=>Text.Replace(x,y{0},y{1})),type text}
        }
    )

in
    ReplaceTexts

Please note, Power Query is case sensetive.

Thanks for this ,but I am struggling to get this to work. I think I am out of my depth with this problem.

Eqa

@Eqa33 

You may try to google, it shall be quite many samples with explanations

@Eqa33 

 

Other option (same setup as @SergeiBaklan)

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToReplace = {"Red", "Black"},
    Replaced = Table.ReplaceValue(Source, each [Product], "",
        (x,y,z)=> List.Accumulate(ToReplace,x,
            (accum,item)=> Text.Replace(accum, item, z)
        ),
        {"Product"}
    )
in
    Replaced

 

 

This is the last step I have in PQ. The column with the words I want to remove is called "DESCRIPTION", the words are BLACK, Black, WHITE, White the list could change with time, and I want to relace them with nothing, just remove them. What is the exact M Code to achieve this, please?

= Table.UnpivotOtherColumns(#"Changed Type3", {"STYLE No.", "DESCRIPTION", "COLOUR", "EXW (RMB)", "EXW (USD/6.7)", "FREIGHT USD", "LCP USD", "LCP AUD/0.70", "WS AUD GST INC", "FINAL WS AUD", "WS MARGIN", "RRP AUD", "ACTUAL RRP AUD", "RETAIL MARGIN", "TOTAL LCP AUD", "TOTAL PROFIT AUD @100% SELL THROUGH", "TOTAL RETAIL SALES EX GST"}, "Attribute", "Value")
best response confirmed by Eqa33 (Brass Contributor)
Solution

@Eqa33 

 

After your current last step that's probably called #"Unpivoted Other Columns" (otherwise replace it below) add the following:

    #"Removed Some Words" = Table.ReplaceValue(#"Unpivoted Other Columns", each [DESCRIPTION], "",
            (x,y,z)=> List.Accumulate({"BLACK","Black","WHITE","White"},x,
                (accum,item)=> Text.Replace(accum, item, z)
            ),
            {"DESCRIPTION"}
    )

to get something like:

let
    ...,
    #"Changed Type3" = ...,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type3",
        {"STYLE No.", "DESCRIPTION", "COLOUR", "EXW (RMB)", "EXW (USD/6.7)", "FREIGHT USD", "LCP USD", "LCP AUD/0.70", "WS AUD GST INC", "FINAL WS AUD", "WS MARGIN", "RRP AUD", "ACTUAL RRP AUD", "RETAIL MARGIN", "TOTAL LCP AUD", "TOTAL PROFIT AUD @100% SELL THROUGH", "TOTAL RETAIL SALES EX GST"},
        "Attribute", "Value"
    ),
    #"Removed Some Words" = Table.ReplaceValue(#"Unpivoted Other Columns", each [DESCRIPTION], "",
            (x,y,z)=> List.Accumulate({"BLACK","Black","WHITE","White"},x,
                (accum,item)=> Text.Replace(accum, item, z)
            ),
            {"DESCRIPTION"}
    )
in
    #"Removed Some Words"

 

But that's not ideal:

  1. The list of words you want to replace is hard-coded
  2. The replacement should be done before unpivoting other columns (for perf. reasons)

==> See in attached file a better option where there's a ToRemove table with your current words (you can add some others) and look at the order of the steps in the MainTable query

 

(gonna be off for weeks...)

Thanks for your help
1 best response

Accepted Solutions
best response confirmed by Eqa33 (Brass Contributor)
Solution

@Eqa33 

 

After your current last step that's probably called #"Unpivoted Other Columns" (otherwise replace it below) add the following:

    #"Removed Some Words" = Table.ReplaceValue(#"Unpivoted Other Columns", each [DESCRIPTION], "",
            (x,y,z)=> List.Accumulate({"BLACK","Black","WHITE","White"},x,
                (accum,item)=> Text.Replace(accum, item, z)
            ),
            {"DESCRIPTION"}
    )

to get something like:

let
    ...,
    #"Changed Type3" = ...,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type3",
        {"STYLE No.", "DESCRIPTION", "COLOUR", "EXW (RMB)", "EXW (USD/6.7)", "FREIGHT USD", "LCP USD", "LCP AUD/0.70", "WS AUD GST INC", "FINAL WS AUD", "WS MARGIN", "RRP AUD", "ACTUAL RRP AUD", "RETAIL MARGIN", "TOTAL LCP AUD", "TOTAL PROFIT AUD @100% SELL THROUGH", "TOTAL RETAIL SALES EX GST"},
        "Attribute", "Value"
    ),
    #"Removed Some Words" = Table.ReplaceValue(#"Unpivoted Other Columns", each [DESCRIPTION], "",
            (x,y,z)=> List.Accumulate({"BLACK","Black","WHITE","White"},x,
                (accum,item)=> Text.Replace(accum, item, z)
            ),
            {"DESCRIPTION"}
    )
in
    #"Removed Some Words"

 

But that's not ideal:

  1. The list of words you want to replace is hard-coded
  2. The replacement should be done before unpivoting other columns (for perf. reasons)

==> See in attached file a better option where there's a ToRemove table with your current words (you can add some others) and look at the order of the steps in the MainTable query

 

(gonna be off for weeks...)

View solution in original post