Results 1 to 6 of 6

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

  1. #1
    Neophyte Lisa77's Avatar
    Join Date
    Aug 2021
    Posts
    3
    Articles
    0
    Excel Version
    O365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365
    How to use Duration function in PQ-->https://efficiency365.com/2018/08/31...s-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"

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    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.
    Attached Files Attached Files

  4. #4
    Neophyte Lisa77's Avatar
    Join Date
    Aug 2021
    Posts
    3
    Articles
    0
    Excel Version
    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?

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365
    No. I calculates Actual Days.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,024
    Articles
    0
    Excel Version
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •