Forum Discussion
Circular error when replacing values in one column off another column
Hello. I am trying to simple replace values in one column off the values of another column IF Dateofcall = 1/1/2019 then I want th evalue in the GMR_Agency to be "RR" so I wrote this:
=Table.ReplaceValue(#"Time-Agency", ---(this is the name of the query (table)- I guess I can call it anything?)
each [GMR_Agency] (this is the field where I want "RR" to be), each if [DateofCall] = "1/1/2019" (this is the VALUE I want to replace with RR in Dateofcall) then "RR" else [GMR_Agency],Replacer.ReplaceValue,{"GMR_Agency"})
I am getting the dreaded 'An error occurred in the ‘’ query. Expression.Error: A cyclic reference was encountered during evaluation.
Is the problem "TIME-AGENCY"?
Hi Anonymous ,
It is worth noting that what is marked in red in the figure below is not the name of the query but the name of the applied step.
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Assume that you have the below table
2. Replace the value of column GMR_Agency with "RR" when the date of column DateofCall is equal to 2019/1/1 in Power Query Editor using Table.ReplaceValue function
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7DcAgDAXAXVwjGZuIJCX/ISz2XyMvpEBprzgzSokcCQurl5umM8r5FZBuKwV2scRNtYIOlgBSv6g10Pmj3kHhu1QWjQGKm+YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GMR_Agency = _t, DateofCall = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"GMR_Agency", type text}, {"DateofCall", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [GMR_Agency],each if [DateofCall] =#date(2019, 1, 1) then "RR" else [GMR_Agency],Replacer.ReplaceValue,{"GMR_Agency"}) in #"Replaced Value"Best Regards
3 Replies
Anonymous As xander2121 suggested you could try a new conditional column, so in Power Query click: Add Column tab in ribbon > Conditional column, then put your conditions in there rather than manual coding that is too easy to get errors.
Then just delete the old column you don't need when you're done.
Alternatively you may find this blog helpful for more advanced scenarios: https://www.howtoexcel.org/power-query/bulk-replace-values/ It's for Excel, but Power Query is the same in Excel and Power BI.
Hello Anonymous
You can try to create a new column. I think Power BI won't let you replace values in a column.
Instead you create a new column with you conditions.
Hi Anonymous ,
It is worth noting that what is marked in red in the figure below is not the name of the query but the name of the applied step.
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Assume that you have the below table
2. Replace the value of column GMR_Agency with "RR" when the date of column DateofCall is equal to 2019/1/1 in Power Query Editor using Table.ReplaceValue function
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy7DcAgDAXAXVwjGZuIJCX/ISz2XyMvpEBprzgzSokcCQurl5umM8r5FZBuKwV2scRNtYIOlgBSv6g10Pmj3kHhu1QWjQGKm+YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GMR_Agency = _t, DateofCall = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"GMR_Agency", type text}, {"DateofCall", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [GMR_Agency],each if [DateofCall] =#date(2019, 1, 1) then "RR" else [GMR_Agency],Replacer.ReplaceValue,{"GMR_Agency"}) in #"Replaced Value"Best Regards
