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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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'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

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

    • powerrdl's avatar
      powerrdl
      Helper I
      9 years ago

      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'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