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
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"
 
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
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?
 
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
Back
Top