Forum Discussion

decarsul's avatar
decarsul
Helper V
5 years ago
Solved

Clean up my code

Good day all,

 

Currently i'm working on creating a measure for net throughput time between two timestamps, where i will eventually cut out the outside business hours and weekends / holidays and i'm planning to do all of this within Query M. (Yes i set a challenge, if i am to read other posts about determining workdays in QueryM).

 

But lets focus on something a little more easy. My question:

Is there a more clean code than the double conversion i have to make in the following?:

 
Duration.TotalSeconds(DateTime.FromText(DateTime.ToText([Date Time Created],"dd-MM-yy 08:00:00")) - [Date Time End])
 
As you can tell from this code, business hours start at 8am, and if the start time of the workorder is before that, i don't want my SLA to be screwed.
 
Let me know if there's an easier / more clean way to fix / lock the time part of a date/timestamp.
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    5 years ago

    Hi decarsul ,

    Would you accept this as a cleaner way?

    Date.From([Datetime created]) & Time.From("08:00:00")

     

6 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    5 years ago

    Hi decarsul ,

    Would you accept this as a cleaner way?

    Date.From([Datetime created]) & Time.From("08:00:00")

     

    • decarsul's avatar
      decarsul
      Helper V
      5 years ago

      Hi Payeras_BI ,

       

      It prevents a double conversion, so it is clearner for sure!

      Wonder if there's more ways.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        You could try

        end_time - max(start_time,8)

         

         

        let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrcyNrAyMFDSUTI0tzI0VYrViVYysACyoIJmViYQQQsrA0OgWpAYkAnWAxI2B8kja7ZAMS0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [st = _t, et = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"st", type time}, {"et", type time}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "nt", each Duration.TotalSeconds([et]-List.Max({[st],#time(8,0,0)})))
in
    #"Aggiunta colonna personalizzata"

         

         

        I would be curious to know what kind of company is the one that counts the working time up to the second. Maybe it's a watch factory? 😁