I'm trying to change column data type in power query

Question

I have problem with data type conversion in power query. I have data about total working hours ("[h]:mm:ss"-in this format)
{]2

when I change data type to General in excel I get different outcome

compare to power query

I don't understand why power query gives me different result.

excel file

`let
Source = Excel.Workbook(File.Contents("C:\Users\Auda_A\Desktop\Data.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"[h]:mm:ss", type number}, {"General", type number}})

in #"Changed Type"`

and this is happening when i try to import data from other excel file, if I'm trying to import data from table (not from other excel file) it works fine and I get correct result.

Answer 1

Power Query and Excel have different "base dates" for date times (31-Dec-1899 vs 1-Jan-1900)

Apparently, if you are importing the data from a File, PQ applies its own base date.

To convert that time into a duration that can be used to compute cost of service, you need to first convert that date into a duration, and then you can convert the duration into a number.

To convert the date into a duration, you subtract the base date for PQ (12/31/1899).

The following M-Code will convert data in an Excel file to the relevant number:

let
    Source = Excel.Workbook(File.Contents("C:\Users\ron\Desktop\Data.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"[h]:mm:ss", type datetime}, {"Number", type number}}),
    durations = Table.TransformColumns(#"Changed Type",{"[h]:mm:ss", each Number.From(_-#datetime(1899,12,31,0,0,0))})
in
    durations

Data.xlsx

Result