none
Date and time not showing correct whe imported to Power query RRS feed

  • Question

  • Hi,

    I have an Excel list that I imported to another Excel file via Power query. A few of the imported columns contains date and time, for example 2013-11-09 20:19. When imported to Power query this will show as 41587.84652777778 and only dates/times with zero hours and minutes will show correctly. When trying to change the data type to Date/Time it returns an error for all cells containing hours and minutes.

    I use a Swedish version of Excel that uses a different time format compared to standard excel and also uses a , as a delimiter between date and time compared to the . used as delimiter in standard excel. Don't know if that can explain this problem?

    Brgds,

    C

    Friday, December 13, 2013 8:37 AM

Answers

  • Do you know which version of Power Query you're using? A bug in this area was fixed for either the October or November preview. If you're using the November preview and seeing this problem, we'd love to get a spreadsheet that demonstrates it. Can you create a small spreadsheet with non-confidential information that reproduces this problem, put it in a shared folder in SkyDrive and post a link? We can download it from there.

    Friday, December 13, 2013 1:27 PM
  • The fix for this problem should be in the next update of Power Query.
    • Proposed as answer by Curt Hagenlocher Friday, January 10, 2014 9:29 PM
    • Marked as answer by csten Monday, January 13, 2014 10:25 AM
    Friday, January 10, 2014 9:29 PM

All replies

  • Do you know which version of Power Query you're using? A bug in this area was fixed for either the October or November preview. If you're using the November preview and seeing this problem, we'd love to get a spreadsheet that demonstrates it. Can you create a small spreadsheet with non-confidential information that reproduces this problem, put it in a shared folder in SkyDrive and post a link? We can download it from there.

    Friday, December 13, 2013 1:27 PM
  • I updated Power query earlier this week, so it should be the latest version. When I created the sample file now the problem appears, so if it is a bug it has not been solved.

    You find the sample file on the link below. 4 sheets with some info on the problem, the data, the query result and one sheet with the data and query result as pictures as it shows in my version of Excel, I guess it will look somewhat different when you open the file due to local date/time settings.

    Appreciate feedback on what you find!

    //Caj

    https://skydrive.live.com/redir?resid=94DF9214E3A6D4!107&authkey=!AEuAXX_kPC7yKaI&ithint=file%2c.xlsx

    Friday, December 20, 2013 9:00 AM
  • What language settings are you using in Power Query? here is what I could see using the latest version of Power Query on the English language:

    1. Create a blank workbook

    2. From File->From Excel , specify your workbook as input

    3. Convert type of columns I and J to Date

    4. Convert type of Columns N, P, R and S to DateTime

    5. Apply and Close

    6. Excel shows the right format, not serial numbers


    Zafar Abbas

    Saturday, December 21, 2013 2:33 AM
  • Locale in Power Query is set to Swedish.

    There are no problems converting columns I and J to Date but when I try to convert columns N,P,R and S to DateTime Power Query returns Error for all values except 1899-12-31 00:00:00.

    //Caj

    Saturday, December 28, 2013 10:12 PM
  • I've tried this with both Office=Swedish, PowerQuery=Swedish and with Office=English, PowerQuery=Swedish and I haven't been able to reproduce this. I have not yet taken the step of installing Swedish into Windows.

    Are you seeing these errors in the editing experience, the resulting worksheet or both?

    If you can see the errors in the editing experience, do you get any extra information by clicking on a cell which contains the error?

    Thursday, January 9, 2014 7:26 PM
  • The errors show up both in the editing view and in the resulting worksheet. The error message given in the editing view is as follows:

    DataFormat.Error: Could not parse the input provided to a DateTime value. Details: 41594.135416666664

    If I paste the time value above into the worksheet and change format, it shows correct. Tried reproducing it again and the same thing happened, both with the data in the file above and with new data that I created.

    However, if I import data from a SQL table, it works fine and shows date and time correctly, so the problem seems to be when importing from file.


    Csten

    Friday, January 10, 2014 9:03 AM
  • That's both quite odd and an interesting clue. This error message comes from a code path that's triggered when we try to convert text to a date. I suspect what's happening is that some layer of code can't parse the text as a number for localization reasons and therefore decides to store it as text. Can I assume that your Windows is set to a Swedish locale as well?

    SQL actually has both a date type and typed columns -- unlike Excel, which effectively treats dates as a way to format numbers and which doesn't enforce any type consistency on a given column. So I wouldn't expect there to be any similarities in this regard between loading from Excel and loading from SQL.


    EDIT: I can now reproduce this problem by changing my Windows locale.
    Friday, January 10, 2014 4:35 PM
  • The fix for this problem should be in the next update of Power Query.
    • Proposed as answer by Curt Hagenlocher Friday, January 10, 2014 9:29 PM
    • Marked as answer by csten Monday, January 13, 2014 10:25 AM
    Friday, January 10, 2014 9:29 PM
  • Great, looking forward to that!

    Thought I had mentioned it but I see that missed that. Yes, my Windows locale is set to Swedish.


    Csten

    Monday, January 13, 2014 11:23 AM
  • Excel 2010

    1. Query from Folder containing XLS files. The spreadsheet has a date column, with whole serial numbers cast in "mm/dd/yyyy" format.
    2. Import into PQ; it will not allow me to format as Date (DataFormat.Error: We couldn't parse the input provided as a Date value. Details:  42249); however, will permit formatting as whole number
    3. If formatted as whole number, I can change formatting after query loaded to spreadsheet to display serial number in date ("mm/dd/yyyy") format
    4. If left formatted as Any, it behaves as if a row number and will not allow formatting as date
      It's almost as if the cell was formatted as text, except it is a number (I can use the cells in formulas)

    BTW: I believe my localization is set to United States however the source file for the query is an external client so who knows to what their locale may be set. Still a bug?

    Tuesday, March 1, 2016 5:42 PM