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

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

[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"}
)

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