How to show the string "blank" if a date field is blank?

[croberts21]

I have PBI Desktop from October 2022. We connect to Postgresql database via an ODBC connection in PBI. I have searched the internet and have not found an answer. 

If the date in a table viz is blank I want to display "blank" in that column. The year of this date is also a slicer. The field Jobs[laststatuschangeddate] is a date type column in Postgresql and also in Power Query. I have tried these formulas as a column: 

Status Changed On = IF(ISBLANK(Jobs[laststatuschangeddate])==0,"blank",Jobs[laststatuschangeddate])

The one above gives me this error: "Expressions that yield variant data-type cannot be used to define calculated column."

Status Changed On = IF(LEN(Jobs[laststatuschangeddate])==0,"blank",Jobs[laststatuschangeddate])

This formula gives me the same error. 

Status Changed On = IF(ISERROR(DATEADD(Jobs[laststatuschangeddate],1,YEAR))=true,"blank",Jobs[laststatuschangeddate])

also gave me the same error.

ISEMPTY() is for a table and will not work in this case. 

I have an idea to see if the field is not a date (it's blank) then show the string "blank". But there is not ISDATE() function. 

I cannot do this in PowerQuery since I cannot put a string "blank" in a date field for blank values. I tried.

Any ideas how to replace the date with the word "Blank"? Any ideas how to replace the Year of this date with the word "blank" if the laststatuschangeddate is blank?

Thank you!

[v-rongtiep-msft · Accepted solution]

Hi @croberts21 ,

There are several ways you can do this but it depends on other factors with your data.  For example, if your column if formatted as a date, the new value you use in the replacements must also be a date.  If you'd like the new value to be text like "Missing" then the column will need to be formatted as text.  It would be best to perform the replacement in Power Query.  You can do that by selecting the column, right-click on the column and choose replace values (or select it from the ribbon).  Assuming your field is formatted as a date, enter any date in the old value field and whatever date you'd like to use for the new value in the new value field and click okay.  Then in the formula bar, you should see the script that Power Query generated for the replacement.  Find the value that you entered as the old value to be replaced and change it to null (the word null without any quotation marks. That should do it.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ahmedx]

 Status Changed On = IF(ISBLANK(Jobs[laststatuschangeddate]),"blank",Jobs[laststatuschangeddate])
OR

 Status Changed On = IF(calculate(max(Jobs[laststatuschangeddate])),Jobs[laststatuschangeddate],"blank")
OR

IF(CALCULATE(max(Jobs[laststatuschangeddate]))<>BLANK(),Jobs[laststatuschangeddate],"blank")

[croberts21]

Thank you but these all produce the error: 

"Expressions that yield variant data-type cannot be used to define calculated column."