Power Query automatically converting total hours into datetime format, hence Tot_Hrs are incorrect

[tskumar]

I have data with total hours column but when i'm importing the data into power query automatically getting converted to datetime format and getting the sum of hours incorrect.  Although I have disabled the detect data type for currect workbook. I'm using the Power Query from Excel 365. 
Please find the screenshots below, request for your guidance 

Table1:

Table:13

Power Query: 

[Anonymous]

Hi @tskumar ,

Try this each minus #datetime(1899,12,31,0,0,0), which can then be converted to numeric hours or with formatted text as Nate or ronrsnfld suggests.

let
Source = Excel.Workbook(File.Contents("C:\Users\ADMIN\Desktop\TestFold\PQtst\SrcFile.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Item] = "Table1" or [Item] = "Table13")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Empid", "Name", "time(Hrs)"}, {"Empid", "Name.1", "time(Hrs)"}),
ToDuration = Table.TransformColumns(#"Expanded Data", {"time(Hrs)", each _ - #datetime(1899,12,31,0,0,0)}),
#"Inserted Total Hours" = Table.AddColumn(ToDuration, "Total Hours", each Duration.TotalHours([#"time(Hrs)"]), type number),
#"Inserted Total Hours2" = Table.AddColumn(#"Inserted Total Hours", "Total Hours2", each Text.PadStart(Number.ToText(Duration.Days([#"time(Hrs)"]) * 24 + Duration.Hours([#"time(Hrs)"])), 1, "0") & ":" & 
  Text.PadStart(Number.ToText(Duration.Minutes([#"time(Hrs)"])), 2, "0") & ":" & Text.PadStart(Number.ToText(Duration.Seconds([#"time(Hrs)"])), 2, "0"))
in
    #"Inserted Total Hours2"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ronrsnfld]

In Power Query, you will need to set the datatype of that column to "duration" and you will see it displayed in the duration format (eg: d.hh.mm.ss).

Power Query cannot display durations the same as Excel. If you need to see it that way, you will have to format a text string.

This is your Table 1 with the time(hrs) column typed as duration:

[ZhangKun]

It seems that my reply just now was not submitted?

Inside Excel, the date is still a number, but it will be displayed as a date. Power query will read this date(not number), and you may think of converting the Time (Hrs) column to number type, but this is wrong because the date system of Excel and Power query has some differences before 1900/3/1. The correct way is to use the date subtraction and convert it to a number, refer to the picture and code below.

Duration.TotalDays([#"time(Hrs)"] - #datetime(1899,12,31,0,0,0))

[ZhangKun]

Even without using type inference, Power Query still thinks it is a date type.

Note: The two columns following time(hrs) are the equivalent dates and numbers in Excel.

If you convert time(Hrs) directly to a number, you will find that the result is wrong (one more day), this is because of the bug in the leap year of 1900.

The correct way is to use the date difference to determine the number of days (Excel uses days to determine the date).

code:

Duration.TotalDays([#"time(Hrs)"] - #datetime(1899,12,31,0,0,0))

[PwerQueryKees]

Please share all power query M code. Use advanced editor and do copy paste of the whole query.

[tskumar]

Hi 
please find the mcode below

let
Source = Excel.Workbook(File.Contents("C:\Users\ADMIN\Desktop\TestFold\PQtst\SrcFile.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Item] = "Table1" or [Item] = "Table13")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Empid", "Name", "time(Hrs)"}, {"Empid", "Name.1", "time(Hrs)"})
in
#"Expanded Data"

[tskumar]

thank you for quick reply watkinnc 🙂

The data comes from another application and there are multiple files, hence it would be difficult to modify each all file each day .. 

I tried above solution but still facing the issue 

[Anonymous]

Yes, but look at your 120:30:00 value in excel. It looks like it's still a date time in Excel, but just formatted as HHH:mm:ss. You need to either apply each  Duration.TotalHours(Duration.From(Number.From([Name of your tm(Hrs) column, however you escape it]))).

But even easier to just make it text in Excel, then bring it in as text.

Power Query is importing the underlying value in the cell, regardless of how it's represented in the cell.

--Nate