Forum Discussion

tskumar's avatar
tskumar
Regular Visitor
1 year ago

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

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 disabl...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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