Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Data showing blank and null

Dears,   I've got a strange thing. The data input is MySQL database. The Part Number column contains "Blank" and the ons look blank.  The "Blank" is really null, so IsBlank() returns "True". Howev...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Check for empty string

count(countrrows(table),table[part_number] = "")

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, amitchandak 

As you suggested, I create a new measure to count rows. Yes! The cells can be filtered by this measure: 

Measure = countrows (FILTER('Query','Query'[Part Number]=""))

 

But, why does the isblank() returns False and True, not True for all cells? I also want to know how does this situation occur, and then we can avoid this in the database.

 

Thank you so much!


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

    HI Anonymous 

    First, for the problem why (Blank) and "" in the data filter in edit queries, that means there are blank(null) value in this column and the cell which has space character in the cell, and when you apply the query into power bi data view, they all will be balnk

    For example:
    Edit QUeriesPower bi data view

     

    Second, for why (Blank)  and "" will in a slicer together. So (Blank) will be in a slicer.

    You must have create a relationship with other tables, please see this post for details

    https://community.powerbi.com/t5/Desktop/Slicer-showing-Blank-as-a-value-when-data-does-not-have-any/td-p/301617

    and (Blank) in the slicer is to filter the data that in other tables which has no related data in 'Query' table, and "" is the data value in 'Query' table which is blank value.

     

    Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks, v-lili6-msft 

       

      The (Blank) and (Null) cells are both in edit Query and Power BI  data view, we can see two of them.  And moreover, there's only 1 table in this Power BI file.
        

       

       

      However, space character can be calculated by Len(), as following screenshot. But the Len((blank) cells) returns 0. What's can be space characters?

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        Just to check what is the information you have on that cell on the query editor make a filter on that column by that "blank" value then rigth click the cell and replace by a zero or something else then check on the formula bar what sort of information it was place on the values being replace if it's "", blank  or something similar.

         

        Regards,

        MFelix