Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 "})
Solved! Go to 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.
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.
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?
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |