default date issue

[kincaids]

I'm having an issue with my date fields that I can't figure out.

1.  Whenever I pull in date columns, Power BI defaults them as Date/Time columns, rather than Date columns (I'm guessing this is because there's data I cannot see driving this).

2.  Whoever created the program I'm pulling data from set a date field that is left blank to default an output of '12/30/1899'.

3.  If I try to replace the value '12/30/1899' with 'null', it doesn't replace the dates even though an equation is created, because it's expecting to find a time component that's not there.

4.  If I change the column from a 'Date/Time' setting to a 'Date' setting, then seek to replace the value same as before, it works perfectly.

5.  However, when I 'Close & Apply', I get an error.  If I view the error, it shows that the error table is empty.  If I delete the error table that was generated and Close & Apply, I get another error table.  If I leave the error table, it seems to work correctly, but I now have an unnecessary added table on the right of the Edit Query window.

Is this a big deal?  Is there a way to get it to stop 'error-ing' when closing & applying?  Is the date information in the table I've tried to correct updated or "applied"?

Here's a screenshot of the query editor window after I've tried to apply the date column change, gotten the error, deleted the query error group, and attempted closing & applying again to get the error table a second time.

Any help is greatly appreciated!

[SnehalSutar]

I am also facing similar issue.

In Power Query I am able to see date as 12/31/1899 however in Power BI report I am able to see it as 2/6/2036 . Expected date is 12/31/1899 as in the backend its same.

Power Query

Power BI report

format of this column is Date/Time format. I have deselected Auto time/date intelligence.
Can you please help me ?

[GilbertQ]

Hi @kincaids, what about if you had to modify the default date from 12/30/1899 to something more recent?

I am going to take a real guess here, and that might be in the Query Editor it has some date in the past that it will not go to previously?

Or an alternative would possibly be, is before changing the data type to date time, leave it as Text, then try and replace the values with NULL? 

NOTE: You can often insert a step before the default "Changed Type"

[Greg_Deckler]

I believe that 12/30/1899 is Power BI's "default reference date". That's why he's seeing that when there are null values. I ran into this when dealing with dates before 12/30/1899 in OSU football data.

[GilbertQ]

@Greg_Deckler ok that makes perfect sense, I guess I had it the wrong way around, but got pretty close 

[kincaids]

So why does their have to be a value there? Why can't the date be blank like other types of cells?

[Anonymous]

Since it seems to be a datetime datatype rather than date, try #datetime(1899, 12, 30, 0, 0, 0) rather than #date(1899, 12, 30) in your ReplaceValue step.

[Greg_Deckler]

@kincaids - I wouldn't stress about the extraneous error table. That was a new feature the PG added recently and I don't think that all of the weirdness has quite been worked out yet.