Forum Discussion

SteveMForm's avatar
SteveMForm
Helper III
2 years ago

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?

Need Help

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      SteveMForm
      Helper III
      2 years ago

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

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    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's avatar
      SteveMForm
      Helper III
      2 years ago

      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