Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two columns which have null values I want to create a new custom column which finds the difference between them producing null values as such.
I am aware that I can convert the null values of two columns to 0 and proceed further but I wish to keep it as such.
I tried using advanced editor and using if conditions in a custom column but couldn't find a way to handle null values.
Code used in custom column:
if [ColumnA] < [ColumnB] then 0 else if [ColumnA] = "null" or [ColumnB] = "null" then "null" else [ColumnA] - [ColumnB]
This is how code looks in advance editor:
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Result ", each if [ColumnA] < [ColumnB] then 0 else if [ColumnA] = "null" or [ColumnB] = "null" then "null" else [ColumnA] - [ColumnB]), #"Result" = #"Added Custom1"{0}[#"Result "]
Error I get:
Expression.Error: We cannot convert the value null to type Logical. Details: Value= Type=Type
PS: This logic works fine in a calculated column but I wish to work this custom column
Can somebody please point out in the right direction?
Thanks,
Indhu
Solved! Go to Solution.
sorry there was typo in my formula, it suppose to be then instead of the
if [ColumnA] = null or [ColumnB] = null then null else if [ColumnA] < [ColumnB] then 0 else [ColumnA] - [ColumnB]
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Try the Replacer function:
Replacer.ReplaceValue(value as any, old as any, new as any)
For Example:
Replacer.ReplaceValue([ColumnA],null,0)
will return 0, so you can use it in your custom column for all the columns that you will use to handle nulls in these columns.
So using this function for any column in your formula will always return a value instead of returning an error.
Hello,
I have a similar situation where I am looking at two columns and creating a custom column based on the two columns.
However, I am not getting the expected result. What am I doing wrong?
@chandni90 you first condition should be like below
if [Active] = true
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
One of these days I'll remember to lower case TRUE in M. Thank you parry2k!
Thank you
I've searched for different ways to do IF or Case statements in power BI and keep running across folks using code simillar to the one in this thred.
i.e. IF logic THEN true ELSE false
However when I try to use this sort of function in Power BI now it requires for the IF statement to being with " IF( " and further makes it look just like it would in standard excel.
i.e. IF(logic,[true],[false])
I'm I missing something here? If there a certain setting in power bi that must be checked to adjust the functions? Was there a recent update?
Emilio
Hey @eizquierdo,
this discussion has been about "if condition then something else somethingdifferent" this is the syntax in the M language used by PowerQuery, you mentioned the more EXCEL like IF(condition, trueresult, falseresult) syntax used in DAX.
Regards,
Tom
Can you just replace the null values with nothing. So it would be a replace values and then put replace null and then leave the replace with section blank?
@nmckbcs thanks for your reply.
Can I please know how do you replace null with nothing?
In home ribbon replace a value doesn't allow to do it without any values
Thanks,
Can you share a sample file?
replace you "null" without quotes null
if [ColumnA] = null or [ColumnB] = null the null else if [ColumnA] < [ColumnB] then 0 else [ColumnA] - [ColumnB]
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
sorry there was typo in my formula, it suppose to be then instead of the
if [ColumnA] = null or [ColumnB] = null then null else if [ColumnA] < [ColumnB] then 0 else [ColumnA] - [ColumnB]
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@indhui just tested at my end and it worked see below
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I figured out what is wrong. I didn't use null case first used that in else if so it didn't work and got error.
The code I used:
if [Column A] < [Column B] then 0 else if [Column A] = null or [Column B] = null then null else [Column A] - [Column B]
Thanks @parry2k 😄 works now
@indhualthough it worked after you moved the first if condition but I'm still not sure why it didn't work when it was first condition, still curious to find out the underline issue.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
weird but felt the same. Will dig it if I can and post here 🙂
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |