Power Query - ReplaceValues with two conditions

[nnouchi]

Greetings PBI Community,

I'm trying to reassign a value in a Customer Orders table that is based off of two conditions from columns within that Customer Orders table. I've found that I need to use the Power Query table function --> Table.ReplaceValues()

I haven't found a similar example where multiple conditions were checked. These Customer_IDs aren't the only ones in my table and thus expect only a handful of values changed.

Here is an example of my code below(code insertion is in bold):

let
Source = Sql.Database("Server", "DB"),
Customer_Orders= Source{[Schema="dbo",Item="Customer_Orders"]}[Data],
#"Replaced Value" = Table.ReplaceValue(#"Replaced Value", [SM_Region], each if Record.Field({[Customer_ID] = 61020 and [SM_Region] = 54 then 21 else , [Customer_ID] = 61040 and [SM_Region] = 65, [Customer_ID] = 90001 and [SM_Region] = 54 then 56,[Customer_ID] = 60180 and [SM_Region] = 54 then 13,[Customer_ID]=60280 and [SM_Region] = 54 then 65,[Customer_ID] = 61000 and [SM_Region] = 54 then 65,[Customer_ID] = 90002 and [SM_Region] = 54 then 56, [Customer_ID] = 90003 and [SM_Region] = 54 then 53},Replacer.ReplaceValue,{"SM_Region"}))
in
Customer_Orders

Any help would be greatly appreciated.

Nic

[ImkeF · Accepted solution]

As an alternative, you could add a new column with the correct values and delete the old column.

That would enable you to work with a "replacements table" that you join on [Customer_ID] and [SM_Region] to your Customer Orders table.

Expand the new value and add a new column that checks if the new value is not null -> take the new value  - otherwise - take the value from the original column.

Less typing and probably easier to maintain.