How do I replace a null value in a date column?

[SteveMForm]

Hi Folks

I have 2 date columns with some null values in, but it wont let me replace the null with text to say 'not specified' as it requires a date.

I don't want any other dates for these null values as I think it will throw graphics, any ideas?

[Gabry]

Can't you just exclude those nulls?

It doesn't make much sense to put not specified because "not specified" where will be shown in the graph?

You can put a date like 01/01/1900 and then filter out this date. 

Or you can make a measure that count this 01/01/1900 dates and says, there are x not specified dates

[SteveMForm]

Hi @Gabry 

I dont want to exclude them- for example, I have one visual using this data as a Gantt which we show a line for the item, which I want for visibility, but the start and end date (the 2 date columns) being not filled (as not known yet) mean it has a line that is 1 single day of todays date, and I'm happy with that.

Any excluding, or replacing with a date then filtering out will remove the item, which I dont want

[dufoq3]

Hi, create new custom column

if [Date] = null then "not specified" else Date.ToText([Date], "yyyy-MM-dd") //you can specify any output in 2nd argument of Date.ToText function

[SteveMForm]

Hi @dufoq3 

so if I create a custom column (will be 2- one for start date and one for end date) and apply that Power Query Formula, will visuals like Gantt or any other visual which uses dates in conjunction with my dimDates table still pick up the date formats correctly? I suppose I can trial it...