Forum Discussion
Imported Time based Data via Excel. Query Editor has presented same data as Date Time
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:
You may change the format to Number in Excel.
https://support.office.com/en-us/article/Format-numbers-f27f865b-2dc5-4970-b289-5286be8b994a
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
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:
