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...
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
    Diamond Contributor
    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.

    • Eqa33's avatar
      Eqa33
      Brass Contributor
      Jul 09, 2024
      Thanks for this ,but I am struggling to get this to work. I think I am out of my depth with this problem.

      Eqa
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jul 09, 2024

        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

         

         

Resources