Converting 1000000 seconds in power query does not give the right days

Lisa77

New member
Joined
Aug 1, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
O365
Hi please see attachment, I need to convert seconds to dd:hh:mm:ss. PQ does not calculate the correct days when it reaches a million seconds it seems. Any help would be appreciated. Thank you.
 

Attachments

  • Time Test for Help.xlsx
    181.8 KB · Views: 7

alansidman

Member
Joined
Oct 1, 2018
Messages
362
Reaction score
1
Points
18
Location
Steamboat Springs
Excel Version(s)
O365
How to use Duration function in PQ-->https://efficiency365.com/2018/08/31/power-bi-seconds-to-duration/

Code:
let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.EndsWith([Name], "_Report")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Service Target Runtime"}, {"Service Target Runtime"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Content",{{"Service Target Runtime", "Service Target Runtime Seconds"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #duration(0,0,0,[Service Target Runtime Seconds]))
in
    #"Added Custom"
 

alansidman

Member
Joined
Oct 1, 2018
Messages
362
Reaction score
1
Points
18
Location
Steamboat Springs
Excel Version(s)
O365
Also, just noted that your manual calculation you are dividing by 8 when you should be dividing by 24 as there are 24 hours in a day not 8.
 

Attachments

  • Time Test for Help.xlsx
    251.5 KB · Views: 5

Lisa77

New member
Joined
Aug 1, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
O365
Hi thank you for the code. I will check it out. I'm dividing by 8 as we are required to measure the time a ticket is logged to the time it is closed within business hours. Will your code calculate business hours only?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
Lisa77, your calculations give odd results for low numbers of seconds; 72 secs (1 min 12 secs) comes out as 3 mins 36 seconds.
In an Excel sheet it's difficult to display dd:hh:mm:ss because that format won't show the days part with more than a month's days (31). It would be nice if it could - it does allow for example, to show more than 24 hours if there are greater than 24 hours using square brackets in a format such as: [hh]:mm:ss, but it doesn't work with days ([dd]:hh:mm:ss). It's possible to display DD:HH:MM:SS that looks right but it would be a string and you wouldn't be able to make sensible calculations with it.
So in the attached I've split off whole (8 hour) days from the hours component into 2 columns. I've added a third column (Decimal8hrDays) which gives you whole days with the fraction of 8 hours as a decimal which agrees with your manual calculations. This you could sensibly do arithmetic on.
 

Attachments

  • ExcelGuru11281Time Test for Help.xlsx
    229.6 KB · Views: 4
Top