Blanks that aren't BLANK(), nor null, nor empty strings (""). What are they?

[ericOnline]

I'm trying to filter out what I think are blank values from a list. Unfortunately, they dont' show as Blank(), don't show as "null" and don't seem to be empty strings ("").

How do I determine the datatype of these values?

How would I eliminate them from the data using DAX?

[Arielcchy]

Thought this has been solved, there is a way to find out in Power Query.

If you go to one of those mistery rows > right click > replace values, it will automatic identify its content and fill it into "Value To Find".

Hope this help.

[az38]

Hi @ericOnline 

it maybe a space or a few spaces in row, for example.

to debug create a calculated column

debug = LEN([ID])

the better way is to execute trim operation for this column in Power Query Editor mode

also try ISTEXT() or ISNUMBER() functions to understand data type

[ericOnline]

@az38 thanks for the idea. What do you make of these results?

- I filtered by Blank(?) value

- Created a new calculated column

Results:

[az38 · Accepted solution]

@ericOnline 

how did you understand this is not the "" ?

try, like

Column = 
SWITCH(TRUE(),
ISBLANK([ID]), "1",
[ID] = "", "2",
"Other")

also it will be a good idea to look at data source in Power Query. Does look the same?

[ericOnline]

Nice! @az38 thanks for this debugging tip. You were right, it was an empty string: