How to Replace Invalid Data in a Column?

[Canknucklehead]

Hi All,

I'm not certain that this can even be done, but I know this is the place to ask.

We have a connection to a DB2 database. In one table there is invalid date information in a column. (And no, I can't clean up the data source, as much as I'd like to).

For Example: Field is Last Date Received. Some dates entered are 9999/12/31 and the default entry if left blank is 0001/01/01. Apparently we can not have a blank entry in this field.

When it imports to PowerBI the 0001 date shows nothing at all in the desktop view of the table. If I go into Power Query it shows as 0001/01/01 in the column. The 9999 date shows as an actual date in the desktop and Power Query windows.

Is there a way to duplicate this column and replace the 0001/01/01 date entry with <null> or <blank>? I'm trying to do some calculations based on date and the visuals don't work - the Last Date Received shows nothing, but when calculating if it is late or early compared to our Purchase Date, Power BI calculates it is -700000+ days late. I've tried using the Remove Errors, but Power BI doesn't recognize 0001/01/01 as an error, but does indicate it is an invalid date format. 😕

Sample visual:

What it should look like:

As always, any and all help is appreciated.

[VijayP]

@Canknucklehead 

In Power Query Right Click on the Column where you are getting 0001/01/01 ,right click Replace values  replace 0001/01/01 with null and that should place null and accordingly you can have cleaned data. Let me know if it works

[Canknucklehead]

This wouldn't work as the column is a Date column and the replaced value needed to be in a Date format. It won't allow me to put in <blank> or <null> or anything other than a date.

[amitchandak · Accepted solution]

@Canknucklehead , In dax you can try a new column like

if([date] = date(1,1,1) || [Date] = date(9999,12,31), blank(), [date])

I have not tested this

[Canknucklehead]

This worked! Thanks very much!