Clean up my code

[decarsul]

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.

[mahoneypat]

Not sure if it is relevant or not, but please see this post and the example M code on calculating working minutes between two datetimes.

Calculate Hours with taking into account Business ... - Microsoft Power BI Community

Pat

[decarsul]

Hi @mahoneypat 

Thanks for the suggestion.

It looks great. ill keep it under consideration

[Payeras_BI · Accepted solution]

Hi @decarsul ,

Would you accept this as a cleaner way?

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

[decarsul]

Hi @Payeras_BI ,

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

Wonder if there's more ways.

[Anonymous]

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? 😁

[decarsul]

On Topic: Interesting approach, but i don't think ill use that bit.

Off topic:

Haha, you really got a point there, but i'm geussing the answer is: a company who wants to overuse their compute resources 😛