Forum Discussion
calculated column doesn't show blanks
Anonymous
This is the intended behaviour. Where boolean logic is involved, blanks are considered as having a numeric value of zero and boolean value of FALSE. What you are essentially saying here is, IF Col 1 = Zero or FALSE, then "No". A boolean comparison of a blank cell to the value of zero ( i.e. IF FALSE = FALSE) equates to TRUE and NOT FALSE, and hence the result. Therefore, one way is to not only compare the values, but also the datatype.
CalcCol = IF ( Table1[Col1] = 1, "Yes", IF ( ISNUMBER ( Table1[Col1] ) && Table1[Col1] = 0, "No", BLANK () ) )
Anonymous
This is the intended behaviour. Where boolean logic is involved, blanks are considered as having a numeric value of zero and boolean value of FALSE. What you are essentially saying here is, IF Col 1 = Zero or FALSE, then "No". A boolean comparison of a blank cell to the value of zero ( i.e. IF FALSE = FALSE) equates to TRUE and NOT FALSE, and hence the result. Therefore, one way is to not only compare the values, but also the datatype.
CalcCol =
IF (
Table1[Col1] = 1,
"Yes",
IF ( ISNUMBER ( Table1[Col1] ) && Table1[Col1] = 0, "No", BLANK () )
)
Thanks Anonymous
It makes sense. But it's confusing and the expectation is not clear because it behaves differently in Edit Queries window.
In the Edit Queries window, I used "Column from Example" option for Adding a column. Had Yes for 1, No for 0 and left nulls as is. That have me three values in the new column. Isn't it the same thing as Boolean logic? I also used Conditional Columns with Rules for 1 and 0. Even in that case, I got Nulls.
I'm perplexed as to the difference in behavior between the two cases: Edit Queries window vs Report.