How to reference a zero length string

[ipisors]

In a DAX formula in power BI, how do I refer to a zero length string (empty string) ?  

Is it two double quotes like "" or two single quotes like ''  ?

Also, how do I refer to a Null value (coming back from sql server datasource), is it just the word Null?  

Example, my formula might be something like IF(column is empty,"N/A",column)

[speedramps]

@Fowmy @AlexisOlson 

I think we all gave answers abour the same time

Worth adding that Power BI automatically trims any trailing spaces in text data types.

Learn here https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

Where as SLQ and EXCEL do not.

This often causes confussion when trying to reconciling totals between SQL, EXCEL and Power BI

👍

[speedramps]

Thank you @ipisors 

Your kind words are much appreciated !

Especially afer just receiving some nasty words from the previous member I tried to help 🙄.

Forums can be a scary place.

Good luck with your null, blank and n/a project. 👍  

[Fowmy]

@ipisors 

IF( OR( ISBLANK( Table[Column] ), Table[Column] = "" ), "N/A", Table[Column] )

[ipisors]

Thank you very much gentlemen!  Appreciate the helpful context and info.  

[speedramps · Accepted solution]

Please download this PBIX onfrom Onedrive

Click here 

The input data contains a record with each type of value:-

• null
• empty
• 1 space
• 2 spaces
• 3 spaces
• and text

The first interesting thing is that Power BI automatically trims any trailing spaces in text data types.

Learn here https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

This means that the 1 space, 2 space and 3 space values will be all be converted to empty !

The next interest thing is that null and empty are treated differently

ISBLANK(measure) will just detect null
see https://learn.microsoft.com/en-us/dax/isblank-function-dax

Whereas IF(measure = BLANK()  treates null and empty as the same !

Answer1 = 
VAR myvalue = MIN(Yourdata[Value])
RETURN
SWITCH(TRUE(),
ISBLANK(myvalue), 1,
myvalue = "", 2,
myvalue = " ",3,
4
)

Answer2 = 
VAR myvalue = MIN(Yourdata[Value])
RETURN
SWITCH(TRUE(),
myvalue = BLANK(), 1,
myvalue = "", 2,
myvalue = " ",3,
4
)

If you are ever in doubt then edit and play with the example I provided

Please clcik thumbs up and accept solution buttton

[AlexisOlson]

Zero length string is "". Null is BLANK().

You can do checks like

IF ( OR ( ISBLANK( [Col1] ), [Col1] = "" ), "N/A", [Col1] )

IF ( LEN ( [Col1] ) = 0, "N/A", [Col1] )

IF ( [Col1] = BLANK(), "N/A", [Col1] )

I don't recommend the last one since "" = BLANK() is true but "" == BLANK() is false.

ISBLANK - DAX Guide

[ipisors]

=blank() is not working in a Filtered Rows fx

I'm using 

= Table.SelectRows(dbo_tbl_PatientVisit_Allergy, each [Description] = Blank() or [Description] = "Unknown")

but it says "the name Blank wasn't recognized

[speedramps]

@ipisors 
you are getting confussed between Power Query "M" and DAX.
They are 2 different programming languages and handles spaces differently

Blank() is a DAX command

You can learn Power Query "M" by clicking the column arrow and then apply or remove filters.

the M code is then displayed ...