Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
decarsul
Helper V
Helper V

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.
1 ACCEPTED SOLUTION
Payeras_BI
Solution Sage
Solution Sage

Hi @decarsul ,

Would you accept this as a cleaner way?

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

Payeras_BI_0-1614334669538.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi @mahoneypat 

 

Thanks for the suggestion.

It looks great. ill keep it under consideration

Payeras_BI
Solution Sage
Solution Sage

Hi @decarsul ,

Would you accept this as a cleaner way?

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

Payeras_BI_0-1614334669538.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Hi @Payeras_BI ,

 

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

Wonder if there's more ways.

Anonymous
Not applicable

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

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 😛

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.