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
——————————————————————————————————–
Be the first to comment