Data showing blank and null

[Anonymous]

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". However, the cells look like blank, the IsBlank () result is "Flase", but len() returns 0. I'm so confued about this.

Could anyone help me with this?

[amitchandak]

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]

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]

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]

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?

[v-lili6-msft]

hi @Anonymous 

Could you please share your sample pbix file for us have a test.

Regards,

Lin

[Anonymous]

Thanks, @v-lili6-msft 

This's from database, it's not allowed to send out of the company. However, when I copy the data into Excel, all the (Blank) and (null) cells turn out to be (null), it's confusing. I can only keep them now. 

[MFelix]

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

[MFelix]

Hi @Anonymous ,

Onthe query editor try to find that specif record and make replacemente by null and see it works to make it blank.

Regards,

MFelix

[Anonymous]

Hi, @MFelix 

Thank you.
These cells are not really null, so Replace Values doesn't work for them. And I even don't know what's in these cells.