Scenario:
Suppose we have a sample table "tbl_Sample" as follows :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.
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
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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.