Imported Time based Data via Excel. Query Editor has presented same data as Date Time

[JamesMidgley]

I have imported Time based data from Excel.  In Query Editor the data that in Excel reads 00:07:45 to show a duration of 7hrs and 45 mins is now 31/12/1899 00:07:45.

First question is why has the Query editor changed the format from Time (as specified in Excel) to DateTime in the Query Editor and stuck a ridiculous date on the front?

Second question is related to data type.  My values are durations.  I have attempted to change the data type to Duration but the Query Editor returns an error.  How do I get around this with the minimum of faffing?

[v-chuncz-msft]

@JamesMidgley,

You may change the format to Number in Excel.

https://support.office.com/en-us/article/Format-numbers-f27f865b-2dc5-4970-b289-5286be8b994a

[JamesMidgley]

@v-chuncz-msft

Thanks for the insight but I fail to see how this can help me.  Changing the cell format to number converts 00:07:45 which represents 7 hours and 45 minutes to to 0.32.  Ideally I do not want to do any data processing in Excel as I'd like to keep the source clean and do any manipulations in Power BI so that they are automated and repeatable. Any additional guidance would be appreciated

[MarcelBeug · Accepted solution]

It looks strange to me that 00:07:45 would convert to 7 hours and 45 minutes and not to 7 minutes 45 seconds.

Anyhow, this works with me:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Import time from Excel.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Duration", type datetime}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Duration", each _ - #datetime(1899,12,31,0,0,0), type duration}})
in
    #"Extracted Date"

Coincidentally I published a video some time ago about durations in Excel and Power Query / Power BI: