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 with COUNT function on Locn column. Similarly, COUNT(Mixed) also returning 5. Is it a bug ?

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

14 Replies

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

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

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

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

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        Anonymous

         

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

         

        Best Regards,
        Herbert