Replace multiple values in the same column in one step

[Anonymous]

Hello everybody,

I am looking to a solution to replace several values in a column, in a single step, to have something like this:

for now, here is what my query looks like: 

let
Source = Excel.Workbook(File.Contents("C:\Users\SESA539111\Desktop\Elvina training powerquery and pivot\POSfiles\POS file Elvina v3.xlsx"), null, true),
BMPTest_Table = Source{[Item="BMPTest",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(BMPTest_Table,{{"BMP", type text}, {"PM0_BUSINESS UNIT", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Digital Energy Transactional","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Home and Distribution","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Low Voltage Transactional","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","UNASSIGNED","Transactional",Replacer.ReplaceText,{"BMP"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","UNSPECIFIED","Transactional",Replacer.ReplaceText,{"BMP"})
in
#"Replaced Value4"

I would like, if it's possible, to gather all these "replace value" steps into one single step in my query. I already saw some similar posts on the community but I haven't been able to solve my issue with one of them.

I am really looking forward for your feeback!

Thanks for your help,

Elvi

[C4YNelis]

Just my two cents for a one line solution, since I couldn't really find what I was looking for and this thread comes up first. Please be aware that this solution only works when replacing full strings, not part of a string:

#"multiReplace" = Table.TransformColumns
(
  SourceTable,
  {
    {
      "ADR_SEAL",
      (baseString) =>
      Table.FirstValue
      (
        Table.FindText
        (
          #table
          (
            {"new", "old"},
            {
              {"Zwart", {"", null, "Z"}},
              {"Blanco", {"B"}}
            }
          ),
          baseString
        ),
        baseString
      ),
      type text
    }
  }
)

In the example above I:

- define a mappingstable with old values (list of values to be found and replaced) and new values,

- return the lines of the mappingstable where the old value is found

- return the first value of the resulting table (the replacement value)

- return the base string if no values are found to be replaced.

Please find a more elaborate example below:

#"Replaced Value" = Table.TransformColumns
(
  #"Removed Other Columns",
  {
    {
      "ADR_SEAL",
      (baseString) => let trimmedText = Text.Trim(baseString) in Table.FirstValue(Table.FindText(Table.Buffer(#table({"newValue", "oldValues"}, {{"Zwart", {"", null, "Z"}}, {"Blanco", {"B"}}})), trimmedText), trimmedText),
      type text
    },
    {
      "ADR_ACTIEF",
      (baseString) => let trimmedText = Text.Trim(baseString) in Table.FirstValue(Table.FindText(Table.Buffer(#table({"newValue", "oldValues"}, {{true, {"J"}}, {false, {"N", "", null}}})), trimmedText), trimmedText),
      type logical
    },
    {
      "ADR_EORI",
      (baseString) => let trimmedText = Text.Trim(baseString) in Table.FirstValue(Table.FindText(Table.Buffer(#table({"newValue", "oldValues"}, {{null, {""}}})), trimmedText), trimmedText),
      type text
    }
  }
)

In this example I also:

1. replace multiple values at once per column

2. trim the inputvalues (we have an ERP that adds a lot of spaces to empty strings unfortunately)

3. buffer the mapping tables

4. change the type to text or logical

5. replace values of multiple columns at once.

Note that the table I supply contains the new value as a first value per record. This is essential because of Table.FirstValue.

so the table structure is like:

#table(
  {"newValue", listOfOldValues"},
  {
    {"firstReplacementValue", {"Value1ToBeReplaced", "Value2ToBeReplaced"}},
    {"secondReplacementValue", {"Value3ToBeReplaced", null, ""}}
  }
)

("" / null values are possible and are just values to be replaced).

It might be wise to test with / without Table.Buffer, as this might break query folding and I'm not sure about the performancegain (if any).

Hopefully others find this useful too.

Cheers,

Niels

Edit: corrected my reply due to an unfortunate mistake in the formula (forgot to return the base value in case there was nothing to replace). Apologies to anyone who suffered this mistake. I've also added a more elaborate example replacing multiple columns and added that this only works for replacing full strings, not parts of a string.

[Fayss]

Add Column > Custom Column:

= Table.AddColumn(#"Filtered Rows1", "New Value", each Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace([Current Value],
"Digital Energy Transactional","Transactional"),
"Home and Distribution","Transactional"),
"Low Voltage Transactional","Transactional"),
"UNASSIGNED","Transactional"),
"UNSPECIFIED","Transactional"))

[tackytechtom]

Hello y'all,

Just wanted to drop off another way on how to solve this namely by utilising a function together with a cleansing table. The idea is to make the whole approach a bit more scalable, by just adding a new rule to the cleansing table. The function would then automatically clean the target table accordng to that new row.

Feel free to check this out in the following blog post:
how to replace several characters in a column in just one power query step. (tackytech.blog)

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Here I created a sample for your reference.M code for your reference. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslMzyxJzFFwzUstSq9UCClKzCtOTC7JzM9LzFGK1YlW8sjPTVVIzEtRcMksLinKTCoFyYFlfPLLFcLyc0oS01Ox6Av1cwwO9nT3c3WBcoMDXJ093TxB/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Value", type text}}),
    #"AllReplace" = [#"Digital Energy Transactional" = "BMP", #"Home and Distribution" = "BMP", #"Low Voltage Transactional" = "BMP",#"UNASSIGNED" = "BMP",#"UNSPECIFIED" = "BMP"],
    #"Replaced Value" = Table.TransformColumns(#"Changed Type",{{"Current Value",each Record.FieldOrDefault(AllReplace,_,_)}})
in
    #"Replaced Value"

BTW, Pbix as attached.

[Zuza]

Hello, i dont see the attachment

[vivekhn]

Hello,

You can use the if else statements to replace multiple categories in a single power query replace value formula.
For example, If you have a status column with the values A,I,T and wish to replace them with Active, Inactive, and Terminated use the formula provided below.

= Table.ReplaceValue(
#"Filtered Rows", 
each [Status],
each if [Status] = "A" then "Active" else if [Status] = "I" then "Inactive" else "Terminated",
Replacer.ReplaceValue,{"Status"}
)

[AnnOminous]

Great approach, and I was able to extend it so that I could apply a value to one column (column_b) based on the condition/value of another column (column_a). My code looks something like this:

= Table.ReplaceValue(
#"Filtered Rows", 
each [column_b],
each if [column_a] = "x" or [column_a] = "y" then "new value 1" else if [column_a] = "z" then "new value 2" else [column_b],
Replacer.ReplaceValue,{[column_b]}
)

[AntoineAB1]

Best answer I have found. I adapted it to my problem, and it worked very well:

Table.ReplaceValue(Source, each [GL_MONTH], each if [GL_MONTH] = "03" then "Mar" else if [GL_MONTH] = "11" then "Nov" else if [GL_MONTH] = "12" then "Dec" else if [GL_MONTH] = "04" then "Apr" else if [GL_MONTH] = "01" then "Jan" else if [GL_MONTH] = "07" then "Jul" else if [GL_MONTH] = "06" then "Jun" else if [GL_MONTH] = "10" then "Oct" else if [GL_MONTH] = "09" then "Sep" else if [GL_MONTH] = "02" then "Feb" else if [GL_MONTH] = "08" then "Aug" else if [GL_MONTH] = "05" then "May" else null,Replacer.ReplaceValue,{"GL_MONTH"})

Thank you!!

[AnnOminous]

Agreed, and I would have marked it as a solution if someone hadn't already marked v-frfei-msft's response as the solution.

[AnkitBI]

Below is how I was able to achieve it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslMzyxJzFFwzUstSq9UCClKzCtOTC7JzM9LzFGK1YlW8sjPTVVIzEtRcMksLinKTCoFyYFlfPLLFcLyc0oS01Ox6Av1cwwO9nT3c3WBcoMDXJ093Tyh/MSkZKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Value", type text}}),
    Custom1 = Text.Combine({"Digital Energy Transactional","Home and Distribution","Low Voltage Transactional","UNASSIGNED","UNSPECIFIED"},","),
    Custom2 = Table.TransformColumns(#"Changed Type",{{"Current Value",each if Text.PositionOf(Custom1,_) <> -1 then "Transactional" else _}})
in
    Custom2

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.