Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi there,
I'm hoping someone can please assist me with this M code I am trying to impliment:
I have a small table called TableCR which has 5 values:
1-EXCELLENT |
2-GOOD |
3-FAIR |
4-POOR |
5-UNSERVICEABLE |
Then I have another main table called Audit. One of the columns within the Audit table is called Rating. What I am hoping for is that I can do a lookup from the Rating column to state that if any of the values within the Rating column does not equal any of the 5 values within the TableCR table, column called "RatingCheck" then I apply a default value,"3-FAIR", to the non matching value.
If the values are within the other table, then leave as is.
This is what I have currently but it's replacing every single value with "3-FAIR" instead of just the non matching values, can anyone please assist me to tweak this to get it to work as hoped?
= Table.ReplaceValue(#"Replaced CRating", each [Rating], each if [Rating] <> TableCR[RatingCheck] then "3-FAIR" else [Rating], Replacer.ReplaceValue, {"Rating"} )
Thank you so much for your time.
Solved! Go to Solution.
Hi @EimearC
Might be simpler to add a Custom Column
if not List.ContainsAny(TableCR[RatingCheck], {[Rating]}) then "3-FAIR" else [Rating]
then delete the old rating column and rename the new column you just created.
Phil
Proud to be a Super User!
Thanks so much @PhilipTreacy for the workaround. I will use that solution.
If anyone else does read this & might be able to solve why my original M Code is currently not working though I would love to understand why that is.
Hi @EimearC
Might be simpler to add a Custom Column
if not List.ContainsAny(TableCR[RatingCheck], {[Rating]}) then "3-FAIR" else [Rating]
then delete the old rating column and rename the new column you just created.
Phil
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |