Tuesday, May 16, 2023

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

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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog