Forum Discussion

powerrdl's avatar
powerrdl
Helper I
9 years ago
Solved

Bug in COUNT function?

  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 ...
Need Help
Show and Tell
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Anonymous

     

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

     

    Best Regards,
    Herbert

powerrdl's avatar
powerrdl
Helper I
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    powerrdl
    Helper I
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
    powerrdl
    Helper I
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        powerrdl
        Helper I
        9 years ago

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

         

        Regards

        Rameen