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.

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

  13. Hi Ken
    Another date issue that might be worth a couple of words. I have fields
    Jun 14 | Moscow
    Jun 15 | Ekaterinburg
    Jun 19 | Saint Petersburg
    Change type and what do I get?
    01/06/2014; 01/06/2015; 01/06/2019 (fine for the US)
    What do I want?
    14/06/2018; 15/06/2018; 19/06/2018
    What is the cleanest way of preventing the issue?

  14. Instead of changing type to date (which will apply YOUR date settings), you need to go to Change Type --> With locale. Set it to Date and choose a Russian region (not sure if English-Russian exists, but if so I'd use that.)

    The Locale settings are all about telling where the data is coming FROM in order to translate it into the correct date serial number.

    To be honest, I'm a bit surprised that you're having an issue here, as Jun 14 should convert properly into the correct date serial number. Are you sure that your regional settings aren't set to show in English US? The secret here is that this is NOT about formatting. When they refresh it in Russia, they'll get their date format. This is about converting to the correct underlying date serial number.

  15. Hi Ken,

    So I have data in two separate queries that I am merging. My "Master" data includes two date columns in either (I've tried both) Date/Time format and Date format. There is a column of ID numbers in text format.

    The second query includes two columns: a list of ID numbers in text format and a Status column, also in text format.

    When I merge these two queries together, even though the second query has no date fields in it, it is causing the date fields in the first query to show the following error message: [DataFormat.Error] Invalid cell value '#VALUE!'.

    I've narrowed it down to this one particular merge (there are multiple merge functions in this query) and the issue seems to pop up as soon as I expand the newly created merge column to display the "Status" information based on matching ID numbers.

    Any ideas what is causing this? It is throwing off my whole query as later on I use the date column to filter out any of the fields within 45 days of the date the report was pulled. Due to the error, I am unable to complete this filter.

    Thank you for your help,

  16. I'm trying to take a date and age it to the next month on the 10th but cannot determine the power query formula. Example: 10/02/2017 would need to be 11/10/2017 in the new column.

  17. Hey Jeff,

    Sort answer... assuming the column with the dates is called [Dates], then this will work: #date(Date.Year([Dates]),Date.Month(Date.AddMonths([Dates],1)),10)

    Longer answer... I think I'll do a blog post on this next week. 🙂

  18. Hi Ken,

    How can I import Duration-formatted cells correctly? I have cells that are custom formatted for HH:MM, and they default to Date/Time when Query pulls in the Excel table. I can't seem to retrieve anything besides an 'Error' within these cells when attempting to convert the columns to a Duration format. Is there a specific format these cells must be in within the original Excel table for Power Query to recognize as (or at least be able to convert back to) a Duration format once loaded in?

  19. Ken,

    Sorry, I should have clarified myself better: I'm importing the data into Power Pivot. It was probably self-evident, but I wanted to make sure that was clear. Thanks!

  20. Hi Alan,

    To covert your DateTime to a duration:
    -Set the data type as a DateTime
    -Change the data type to a Decimal Number
    -Change the data type to a Duration (when asked, make a new step, don't replace the old one)

    At this point you should be able to extract any portions of the duration via Add Column --> Duration

    A couple of things to keep in mind:
    -If you are only interested in times, you may want to start by converting the column to Time Only. If you don't, and it is today's date, it will make for some large hourly durations.
    -Power Pivot only understands DateTime, numbers and text. So you'll need to format any durations as something else before you load there.

    Hope this helps!

  21. I am not sure if this is a recent problem but I have seen this in both excel 2013 and in 2016. I have a csv where all the fields are being seen as text by default by PQ. I am in Australia the date format within the csv is YYYY-MM-DD. PQ automatically creates 3 steps (1) Source, 2) Promoted Headers and 3) Changed Type) At the changed type step the date fields are seen as Dates and all looks good until I load the data into PowerPivot where it looks like it is loading and then fails stating date fields still being recognised as text. It is as if Power Query is ignoring PQ changes and thinks it is attempting to load text data into date fields which it cant do. I seem to remember that PQ would read csv fields by default as "Any" and not Text which would allow the date fileds to be recognised as dates and load into Power Pivot without any problems. Is there a way for PQ to recognise the date field in csvs as dates or "Any" again? At the moment the only way I can get Power Pivot to load a csv as a date is to 1st link the csv into MS Access and then have PQ point to the MS Access linked table because the link specification used converts all date fields as short date. Any advise would be appreciated.

  22. Hi Glenn,

    This post is REALLY REALLY old, and there has been new functionality added to make it easier to deal with this. It's so important, that it's actually one of the first lessons we teach in the Power Query Academy.

    What you need to do is right click the column header and go to Change Type --> Using Locale. Set it to a Date, and then choose the region that your data is coming from. So if the data is provided in a US format, but you use AUS format, you'd choose English - United States - not English - Australia. That will then apply the correct date serial number, which will then allow Power Query to display the dates in the format set in your Windows Regional Settings.

    I hope this helps, as there is no reason that you should need to go through Access. This feature is SPECIFICALLY designed to help import those dates from Text and CSV files. Just make sure you delete the auto-created Change Type step first, as Power Query processes in sequential order. If you try to convert using Locale AFTER the original change type step it's too late as the dates are already broken.

  23. I'm using Excel 2016 on a Windows 10 platform. I've created a query that has a input column of a four digit yyyy which I parse into a date/time field choosing the last day. The field shows correctly in the Workbook Query Preview but shows up incorrectly in the loaded Excel table. The transformation seems to be from a date/time "12/31/2012 12:00:00AM -5:00" to text "1/1/2017 0:00". I am mystified. Note the change in year. It seems to have pushed the date forward by 4 years and a day. Any ideas?

  24. Paul, that's very strange for sure. You're sure the column is defined as a datetime type in Power Query before loading? What happens if you separate the date and time columns prior to loading to Excel. Does it return the correct dates then?

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.