Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

COUNTBLANK function issues

I have a COUNTBLANK function in my Power BI to show the columns where someone has forgotten to put in a number of details but it counts where there is an empty entry in the name column. For some reas...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - I would use Clean and Trim operations in Power Query to get rid of any stray characters. You could also try:

     

    Count Blanks =
  COUNTROWS(FILTER(ALL('Table'),ISBLANK([Column]) || [Column]="" || LEN([Column])<3))

     

    Also, why are you not flagging rows with missing data versus columns? 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , is it blank or have some space.

 

countrows(filter(table,isblank(Table[Column]) || Table[Column]= "" || Table[Column] = " " ))

 

or try to use trim

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak Annoying that function still returns (Blank) meaning there is nothing to count, however, I can see there is a space in my data column still.

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous - I would use Clean and Trim operations in Power Query to get rid of any stray characters. You could also try:

       

      Count Blanks =
  COUNTROWS(FILTER(ALL('Table'),ISBLANK([Column]) || [Column]="" || LEN([Column])<3))

       

      Also, why are you not flagging rows with missing data versus columns? 

    • v-xuding-msft's avatar
      v-xuding-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      Have you resolved this issue? Is the answer above helpful? If you still need help, can you please share more details, like your sample data and your expected results. Thanks!