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.

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

  8. I had a similiar problem where the column would come in as serial number in text format, and changing type to date caused an error.

    I fixed the dates by converting the column to a whole number, and then doing an additional conversion to date. That worked for me.

  9. Regarding setting the Data Types in the last step of the query, does that apply to a query you know will be a source for other queries, or just the last one that will be loaded to the data model or sheet?

  10. Hi Ken,

    Have you had issues of data integrety when appending mutliple tables?

    We seem to lose a significant amount of data when we append tables with same columns, data, formats, etc.

    In reviewing each table, we summed up one benchmark column across each table ( Cost column is same in each column in Decimal format) and their sums equaled X, but when we appended each table ( all 4 into 1 query ) the sum came out as Y.

  11. Hey Ken, thanks for the article. Would you say then it is a better practice to perform all shaping (including date formatting or numeric formatting etc...) in Power Query (the query editor) before loading to the data model?

    I had been performing these 'formatting' steps when using the columns in visuals (talking about Power BI Desktop here) after loading the query simply because some formatting can only be done in the data model anyway, like currency formatting, etc... Any advice?

  12. Hi Nate,

    What you see within the editor is not formatting, it's data typing. When it gets to Power BI (or the Data Model), that's where you see the formatting taking place. They are distinct things, and need to both be done.

Leave a Reply

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