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.
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"?
Solved! Go to Solution.
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
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
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |