Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III
Helper III

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

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?

Responsive Resident
Responsive Resident

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

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

Super User
Super User

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



Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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...

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.