Forum Discussion
How to reference a zero length string
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)
Please download this PBIX onfrom Onedrive
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-daxWhereas 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
8 Replies
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
👍
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.
=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
ipisors
you are getting confussed between Power Query "M" and DAX.
They are 2 different programming languages and handles spaces differentlyBlank() 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 ...
Please download this PBIX onfrom Onedrive
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-daxWhereas 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
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. 👍
Thank you very much gentlemen! Appreciate the helpful context and info.
