How to Replace values from a Column based on multiple conditions in Power Query

How to Update values from a Column based on multiple conditions from other Columns in Power Query

Scenario:

Suppose we have a sample table "tbl_Sample" as follows :

From this table we need to update/replace the values of [DiscountRate], based on the below Conditions :
If [ProdID] in {124,126, 128} or [ChannelType]="Retail"  then we need replace the respective values with "0.25" in [DiscountRate], otherwise we needs to return the original values as is.

We can achieve this requirement using the below M-Query :

let

    Source = Excel.Workbook(File.Contents(DataSource), null, true),

    Data_Sheet = Source{[Item="tbl_Sample",Kind="Sheet"]}[Data],

    PromoteHeaders = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),

    ReplaceOneColMultiCriteria = Table.ReplaceValue(PromoteHeaders,

    each [DiscountRate],

    each if ( List.Contains({124,126, 128},[ProdID]) or [ChannelType]="Retail") then 0.25 

    else [DiscountRate], Replacer.ReplaceValue,{"DiscountRate"})

in

    ReplaceOneColMultiCriteria

Result:

We can also try to update the Multiple Columns based the Multiple Conditions, however, the logic is working for only 2 Columns and for 3rd Column it is not working as expected:

let

    Source = Excel.Workbook(File.Contents(DataSource), null, true),

    Data_Sheet = Source{[Item="tbl_Sample",Kind="Sheet"]}[Data],

    PromoteHeaders = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),

    ReplaceMultiColMultiCriteria = Table.ReplaceValue(PromoteHeaders,

    each [ChannelID], 

    each if List.Contains({124,126,128},[ProdID]) or [ChannelType]="Retail" then 0.25 

    else Table.SelectRows([ChannelID]),

    Replacer.ReplaceValue,{"SegmentID","ChannelID","DiscountRate"})

in

    ReplaceMultiColMultiCriteria

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------