Replace formula for mutliple conditions

[Anonymous]

Hi, Looking to replace all bill period code 31 to 19 and then 10 to 1 for one biller_name VTNS. Below is the formula I am trying and getting a token literal expected error. Can I do a replace on the column as a step rather than adding a custom column? If so, I need to add additional column, named "bill period trans_2".

If the data fits neither condition, I want the bill period to remain the same

Table.ReplaceValue(#"New Column", each [BILLING_PERIOD_CD], each if [Biller Name Lookup.Biller Name] = "VTNS" and [BILLING_PERIOD_CD] = "31" and then "19" else [BILLING_PERIOD_CD],Replacer.ReplaceValue,{" BILLING_PERIOD_CD "})

[v-jingzhang · Accepted solution]

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.

[Anonymous]

Could you tell me 1. What is wrong with my code above since it is an add column if then else statement? 2. Can I add two if statements to cover the change in both bill periods for the one biller rather than two columns?

[AlexisOlson]

A custom column is easiest since a column transformation doesn't allow for referencing other columns.

It is possible with row transformations though. See for example my answer to a similar question here:

https://stackoverflow.com/questions/65887249/table-transformcolumns-by-columns-value

[Anonymous]

Specficially, I'm not getting any syntax errors now, but am getting an error that "New Column" doesn't correct or isn't spelled right - I thought this function was adding that new column as well?