Forum Discussion
Bug in COUNT function?
Anonymous
COUNTA can count TRUE/FALSE column but COUNT still raises an error.
Best Regards,
Herbert
For Product ID 2, what exact data is in Locn? This count of 5 would indicate to me that that field is not blank. For example having the data " " is not considered blank or empty. In your Query editor, you might want to apply the Trim function to that column. You can do this by right clicking on the column Transform -> Trim
Hi Anonymous
Much thanks for your response.
This is dummy data created to practice the COUNT/ COUNTA functions. As per my understanding, COUNT is supposed to give error when used with a Text column, but it doesn't. Also, the location for Product ID 2 is empty, not " ". If COUNT is working similar to COUNTA, what really is the difference between the two ?
Regards
Rameen Dhall
Count vs CountA: CountA will essentially count every row. Count will only count the rows that are non-blank.
Dummy data or not, my expectation is that you are getting a similar result due to the count seeing " " as data. It needs to be understood that " " or "" is not blank, nor is it null. I want to stress this point as you could be incorrectly thinking you will get a lower count because you believe that value is empty, when the computer sees it as the character space (i.e. unicode 0020).
Hi Anonymous
I don't see the option of Transform on right clicking Locn. So I created another column : Column = TRIM(CountDataCSV[Locn]) and changed the function to refer to the new column : countlocn = COUNT(CountDataCSV[Column]). Still getting count as 5 :(
Regards
Rameen Dhall
Hi powerrdl
First lets start with your "Edit Queries" section of Power BI. There is a button for that on the Ribbon under Home.Your table should look something like this if it is correct:
See how row 2 for Locn is coming up as null? That what i'm hoping to see in your data, but i'm expecting in your data source that it might not be showing that. Right clicking on Locn here is where the Trim option is from my earlier post.
Hi Anonymous
You are right ! I right clicked in Edit Queries and Transformed as instructed, but it is still not giving 'null' for Product 2 :(
Regards
Rameen Dhall
Thats ok! This just means we are making progress. Doing this normally works in Excel and i expected that to work here, but it looks like it hasn't. I just tried on my sample set and got the same result. No matter, lets add another step. Still in that query editor, highlight the column and right click and select "Replace Values"
In the pop up, leave the 'Value to Find' empty and write the word null into 'Replace With'. It should detect that null is a keyword and then your data should look like my screenshot.
Hi Anonymous
I was importing data from a CSV, when I changed it to Excel, started getting 'null' as desired and count as 4 !!
But I still have a confusion, definition of COUNT on MSDN site says : The COUNT function counts the number of cells in a column that contain numbers.
As per a video of Curbal : https://www.youtube.com/watch?v=V8wYTjKyvgk , it gives error when used with Text column, then how come here it is counting text too ?
Regards
Rameen
Yeah thats a real mystery to me too. I've never played with it enough to notice. You are right about what MSDN says and I don't see it behaving this way.
Still, at least you learned something about the nulls!