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 have a table shown in the following screenshot:
What I want to do is: if column Color is Red AND column Like is null, replace the null in the Like column with "N".
Therefore, the Like column of Apple, Cherry and Dragon fruit should be N.
However, the following syntax does not work:
= Table.ReplaceValue(#"Changed Type", each [LIKE], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceText,{"Like"})
Does anyone know how to do this?
Thanks
Solved! Go to Solution.
OK, now the nulls are real nulls (as they are in italics now).
So you have to go back to without quotes and use a different replacer function (ReplaceValue instead of ReplaceText) like so:
Table.ReplaceValue(#"Replaced Value", each [Like], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceValue,{"Like"})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @lf963 ,
You can try the following code and use the Replacer.ReplaceValue function. Hope that helps!
= Table.ReplaceValue(#"Replaced Value1", each [Like], each if [Color] = "Red" and [Like] = null then "Y" else null, Replacer.ReplaceValue,{"Like"})
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
OK, now the nulls are real nulls (as they are in italics now).
So you have to go back to without quotes and use a different replacer function (ReplaceValue instead of ReplaceText) like so:
Table.ReplaceValue(#"Replaced Value", each [Like], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceValue,{"Like"})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This works!!! Thank you!!!
Did you also try putting null in quotes?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Yes I put the null in quotes in the "Replaced Value1" step in the following screenshot but it doesn't help.
..and the M-language is case sensitive, so you have to adjust to this:
Table.ReplaceValue(#"Changed Type", each [Like], each if [Color] = "Red" and [Like] = "null" then "N" else [Like],Replacer.ReplaceText,{"Like"})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Tried lower case Like but no luck
Hi @lf963 ,
your null does look like simple text and not the "real" null. So this would probably work:
Table.ReplaceValue(#"Changed Type", each [LIKE], each if [Color] = "Red" and [Like] = "null" then "N" else [Like],Replacer.ReplaceText,{"Like"})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF,
The screenshot shown in my original post was manually created. I should've made them real "null". The following screenshot is the correct one:
The following syntax doesn't work:
= Table.ReplaceValue(#"Replaced Value", each [Like], each if [Color] = "Red" and [Like] = null then "N" else [Like],Replacer.ReplaceText,{"Like"})
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.