cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Power Query time format '37:30:55' bug

Hello! I encountered the following problem when working in PQ with data in the format '37: 30: 55 '. If you try to retrieve data in this format using Excel.Workbook, the data may be incorrect. Please see an Example.

The table has 2 rows and time and number columns. The values ​​in the first row are greater than the second in both columns. But if you connect to the file using Excel.Workbook (see query Time in Example.), then the data in the second row of the time column will be larger, which is incorrect.

 

How can I solve this problem?

3 REPLIES 3
Community Support
Community Support

Hi @Murderface_ ,

 

Actually, this is a bug in Excel about leap year.

You could refer to this link: https://en.wikipedia.org/wiki/Year_1900_problem

Microsoft Excel (using the default 1900 Date System) cannot display dates before the year 1900, although this is not due to a two-digit integer being used to represent the year: Excel uses a floating-point number to store dates and times. The number 1.0 represents January 1, 1900, in the 1900 Date System, or January 1, 1904, in the 1904 Date System and was the default for Macintosh prior to Excel 2016. Numbers smaller than this display as a #VALUE! error.
For compatibility with Lotus 1-2-3, the 1900 Date System incorrectly accepts February 29, 1900, however, 1900 was not a leap year.

So there isn't a "02/29/1900" date, because 1900 is not a leap year. 

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Good day! I know about the problem of 1900, then in my case we are talking about time in the format '37: 30: 55 ', and PQ treats it as a date and if this date is 29 feb 1900, then I lose 24 hours.
Regular Visitor

The problem is that the time is 1440: 00: 00 in numerical format = 60, and in the date and time format it is 29 feb 1900, but in PQ this date is interpreted as 01 mar 1900 and the difference is 24 hours.

 

How can I solve this problem?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors