Dealing with blank dates

Power Query
1

I have a column that contains dates, but some are left blank or have a 0. When the date is 0 or blank, I want it to display the current date plus 180 days. I first selected the column and replaced value of blank or 0 with 19010101, then built a custom column

if [columnname] = 19010101 then Date.AddDays(DateTime.LocalNow, 180) else [columnname]

but this is not working. Any suggestions?

2

Try something like this:

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    ChangedDateType = Table.TransformColumnTypes(Source, {{"Dates", type date}}),
    AddDays = Table.AddColumn(
            ChangedDateType, 
            "Custom", 
            each 
                if [Dates] = #date(1899, 12, 30) then 
                    DateTime.Date(Date.AddDays(DateTime.LocalNow(), 10))
                else 
                    [Dates]
        ),
    ChangedDateType2 = Table.TransformColumnTypes(AddDays, {{"Custom", type date}})
in
    ChangedDateType2

image

3

It actually pulls dates in like this from the excel spreadsheet. When I convert this column to Dates, I get an error in the columns that have a 0. I am not well versed in editing in Advanced Editor so a Custom Column solution would be best for me, if available. Thanks for your help.

image
image183×592 2.78 KB

4

First convert the data to text and then to dates.

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Dates", type text}}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", {{"Dates", type date}})
in
  #"Changed Type1"

image
image797×363 15.4 KB

image
image928×381 16.6 KB

you can replace 0 with 19010101

2 Likes
5

Thank you. It is working now.

6

Awesome!!