38 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. Hey Ken, how can I convert the date when Power BI Desktop import it as General format from Excel. Look at this: http://d.pr/i/7f1S/24UhJfwr

5. It's surprisingly easy. Just right click the column --> Change Type --> Date. That should take care of it for you.

6. Hello Ken,

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

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

8. 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?

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

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

11. 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?

12. Hi Phil,

    Definitely for the ones that will go to the data model or worksheet. The others are optional. 🙂

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

14. 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?

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

16. 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?

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

18. 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,

19. Hey Riad,

    Can you post this in our help forum at http://www.excelguru.ca/forums ? It would be much easier to help if we could see a small sample that shows the error.

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

21. 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. 🙂

22. 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?

23. 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!

24. 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!

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

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

27. 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?

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

29. Hi Ken, I have a similar issue except my field does have a data type of 'Date'. In Excel 2013, the loaded data appears correctly. In Excel 2016, the cells appears as desired when you first open the spreadsheet. Once you refresh the data, the field flips back to its numeric equivalent. From there I go back to Power Query editor and can see that the field is still defined as Date. Any thoughts? I've tried formatting the entire excel column as 'Date'. I've applied Changed Type with Locale to the field. Nothing appears to work.

30. Hi Christine,

    Try selecting a cell in the table, go to Table Tools --> Design --> Properties. Uncheck preserve cell formatting and then see if a refresh changes the behaviour.

31. Hi Ken,

    I tried that and the dates still formatted back to Excel's numeric equivalent.

32. Christine, out of curiosity, is the very last line of your query to force it to a Date, or did you do it higher up?

33. Yes, it's at the end.

    I have filters the users selects and I pass that into my sql call. For the sql, I'm just selecting all the columns (no formatting applied). Once I get the result, I apply ChangedType.
    Power Query recognizes the field as a Date/Time and I thought that maybe the time component was the cause of the date flipping to numeric. So, I apply the ChangedType functionality. Regardless of the type (Date/Time or Date), it still flips.

    Most annoying part is that I can't reproduce this behavior in Excel 2013.

34. That is very weird. The next thing I would try is this... on a copy of the workbook, delete the worksheet that holds the output table. (This will set the query to "Connection Only".)
    Then right click in the queries pane and change the Load To destination to a table on a new worksheet. Does it still do the same thing?
    If it does, then the issue is something to do with the output table. If it doesn't, then it sounds like a bug with Power Query in your version of Excel.

35. Hi Ken,

    My file has a column with dd:mmm:yy hh:mm format. However when i tried to use power query editor, the time becomes 00:00.

    E.g. 31/Oct/2019 6:30:00 PM -> 31/10/2019 00:00

    Please help me.

36. Hi Ben,

    A couple of things to check...
    1) Have you tried to set the data type before any other change type steps? (If you have a step that drops the times, you can't get them back in a later step)
    2) Did you just use the transform to DateTime?

    I might try setting the transform Using Locale, converting it to a DateTime from English (Canada). I believe that format should work for you.

37. I tried everything, and was still getting this issue in one of my spreadsheets. So I first changed to "text", then changed to "date/time", and then to "date". Worked

38. Hi Klaus. Yes, this is a common issue when converting datetimes from text to a date. You can't go directly from text to date if a timestamp is also on the data. What I do is convert to a datetime first, then add a new step to add a date (not replace the step.)

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