Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replace formula for mutliple conditions

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 "})

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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?

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.