Bug in COUNT function?

[powerrdl]

I am getting same output with COUNT and COUNTA functions for all columns of above data. COUNT(Locn) gives output as 5 even though there are 4 entries and no numeric entry. Not getting error with COUNT function on Locn column. Similarly, COUNT(Mixed) also returning 5. Is it a bug ?

[v-haibl-msft]

@powerrdl

I’ve got response from the Product Team.

This is a recent enhancement to the COUNT function. Previously COUNT function doesn't count strings but just raise an error. We have since enhanced the function to count strings as well. We'll inform the documentation team to update the online document accordingly.

Best Regards,
Herbert

[Anonymous]

That raises a follow up question, as mentioned in OP.  What will be the functionality difference between COUNT and COUNTA?

[v-haibl-msft · Accepted solution]

@Anonymous

COUNTA can count TRUE/FALSE column but COUNT still raises an error.

Best Regards,
Herbert

[Anonymous]

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

[powerrdl]

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

[Anonymous]

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.

[powerrdl]

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

[Anonymous]

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!

[powerrdl]

So back to my original question -- Is it a BUG ?!! Or has the functionality been changed ?

Regards

Rameen

[v-jiascu-msft]

@powerrdl

Hi,

“Count” acts strange in Power BI, which is different from the documents. I have reported it internally to Power BI Team and I will post here as soon as I get feedback.

Best Regards!

Dale

[powerrdl]

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

[Anonymous]

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.

[powerrdl]

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

[Anonymous]

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).