Forum Discussion

Eqa33's avatar
Eqa33
Brass Contributor
Jul 07, 2024
Solved

Text.Remove a specific word in Power Query

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

BI & Data Analysis
power bi
  • Lorenzo's avatar
    Lorenzo
    Jul 10, 2024

    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...)

    • Eqa33's avatar
      Eqa33
      Brass Contributor
      Jul 08, 2024
      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.
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jul 08, 2024

        Eqa33 

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

        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.

Resources