Date Formats in Power Query

Date formats in Power Query are one of those little issues that drives me nuts… you have a query of different information in Power Query, at least one of the columns of which is a date.  But when you complete the query, it doesn’t show up as a date.  Why is this?

Demonstrating the Issue

Have a look at the following table from Excel, and how it loads in to Power Query:

SNAGHTML157a9ddf

That looks good… plainly it’s a date/time type in Power Query, correct?  But now let’s try an experiment.  Load this to the worksheet:

image

Why, when we have something that plainly renders as a date/time FROM a date format, are we getting the date serial number?  Yes, I’m aware that this is the true value in the original cell, but it’s pretty misleading, I think.

It gets even better

I’m going to modify this query to load to BOTH the worksheet and the Excel data model.  As soon as I do, the format of the Excel table changes:

image

Huh?  So what’s in Power Pivot then?

image

Curious… they match, but Power Pivot is formatted as Text, not a date?

(I’ve missed this in the past and spent HOURS trying to figure out why my time intelligence functions in Power Pivot weren’t working.  They LOOK so much like datetimes it’s hard to notice at first!)

Setting Date Formats in Power Query

When we go back and look at our Power Query, we can discover the source of the issue by looking at the Data Type on the Transform tab:

image

By default the date gets formatted as an “Any”.  What this means to you – as an Excel user – is that you could get anything out the other end.  No… that’s not quite true.  It means that it will be formatted as Text if Power Pivot is involved anywhere, or a Number if it isn’t.  I guess at least it’s consistent… sort of.

Fixing this issue is simple, it’s just annoying that we have to.  In order to take care of it we simply select the column in Power Query, then change the data type to Date.

Unfortunately it’s not good enough to just say that you’ve set it somewhere in the query.  I have seen scenarios where – even though a column was declared as a date – a later step gets it set back to Any.

Recommendations

I’ve been irritated by this enough that I now advise people to make it a habit to set the data types for all of their columns in the very last step of the query.  This ensures that you always know EXACTLY what is coming out after all of your hard work and eliminates any surprises.

9 thoughts on “Date Formats in Power Query

  1. Pingback: Excel Roundup 20150209 « Contextures Blog

  2. I had a similar problem to Date Format in Power Query. In excel I have added a column "DateofReport" filled with the date of the report (example 1/14/2015). I load into power query (office 2010) and the date looks fine. Transform shows "Date". Once I "Close and load" I see the date change to the serial number (42018) then change again to the wrong date (1/15/2019).
    PowerBI desktop works correctly but I can't export to excel.

    Resolved by unchecking the option "Use 1904 date system" in excel worksheet.
    https://support.microsoft.com/en-us/kb/214330

    Thanks,
    David

  3. Interesting. I can see where this becomes and issue, although this is the first time I've actually heard of anyone using the 1904 date system.

    In truth, it would probably be easy enough to fix if you needed the 1904 dates. Just create the normal dates, add a custom column and use a formula like:
    =Date.AddDays([Column1],-1462)

  4. Hello Ken,

    I have the same problem Lucas. The solution did not work for me. Appears error. Any idea how to solve?

  5. It it is creating errors, then it is most likely trying to convert a MDY into DMY or vice versa. This happens when the Windows system settings are different than the date format used in the data source.

    What I would suggest is, instead of using Change Type --> Date, use Change Type --> Using Locale. Set the data type to date, but set the format to that which the data comes from.

  6. Hi Ken,

    I've been having a similar issue, and none of the above fixes is working all of the time.
    I have a column with dates in it, which are text in the source file. Oddly, some of them are formatted like this "08/11/2016", and others look like this "08/11/16". It's the "08/11/16" version that is causing problems. If I change the type to date, even using locale, those ones return "error".
    I'm using Excel 2016.
    Any ideas?

  7. So I would probably do something like this in a custom column:

    =if Text.Length([DateColumn]) = 10 then [DateColumn] else Text.Start([DateColumn],6) & "20" & Text.End([DateColumn],2)

    Change DateColumn to the name of the column that holds your dates. This should insert a 20 anywhere where it is missing, providing that the format always uses 2 digit months and days. After you've done this, the resulting column should be able to be converted into dates without issue.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *