Replace formula for mutliple conditions

Hi Anonymous 

If you want to add a custom column, please try these codes:

= Table.AddColumn(Source, "Custom", each if [Biller Name] = "VTNS" and [BILLING_PERIOD_CD] = "31" then "19" else if [Biller Name] = "VTNS" and [BILLING_PERIOD_CD] = "10" then "1" else [BILLING_PERIOD_CD])

If you want to replace values in the original column, please try these codes:

= Table.ReplaceValue(#"Duplicated Column",each [BILLING_PERIOD],each if [Biller Name]="VTNS" and [BILLING_PERIOD]="31" then "19" else if [Biller Name]="VTNS" and [BILLING_PERIOD]="10" then "1" else [BILLING_PERIOD],Replacer.ReplaceValue,{"BILLING_PERIOD"})

Here are the complete codes you can copy to a blank query to check the behaviors. I add a duplicate column [BILLING_PERIOD] of column [BILLING_PERIOD_CD] to perform the replace step to make it observed more clearly.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZU0lEKC/ELVorViVYyNEDhmSLzjFDksOiLBAIIzwjOiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BILLING_PERIOD_CD = _t, #"Biller Name" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Biller Name] = "VTNS" and [BILLING_PERIOD_CD] = "31" then "19" else if [Biller Name] = "VTNS" and [BILLING_PERIOD_CD] = "10" then "1" else [BILLING_PERIOD_CD]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "BILLING_PERIOD_CD", "BILLING_PERIOD"),
    Custom1 = Table.ReplaceValue(#"Duplicated Column",each [BILLING_PERIOD],each if [Biller Name]="VTNS" and [BILLING_PERIOD]="31" then "19" else if [Biller Name]="VTNS" and [BILLING_PERIOD]="10" then "1" else [BILLING_PERIOD],Replacer.ReplaceValue,{"BILLING_PERIOD"})
in
    Custom1

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.