Latest Enterprise DNA Initiatives

Power Query Working Hours Between two Data Times

Hi there,

I have to work out the duration of hours lost between when an Incident was Created and When it was closed.
Working Hours are from 8 - 16:30 Monday to Friday and 8 - 12 on Saturday.

I have created a Holidays file and Merged it with Date Calendar and the Table in Question.

I need your brain.
Major (8.1 MB)
DownTime.pbix (826.3 KB)

Hi @SammiP,

Refer to this topic: Time format 37:30:55 greater than 24 hours | post 10

You just need to make this small change to the code to include Saturdays

// List of days without Sundays
ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)<>1),

I hope this is helpful

Hi @SammiP, did the response provided by @Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

My knowledge of Power Query is not at this level yet. I added the function. I don’t know how to add the list of Holidays to the function. I figured out how to convert my holidays to a List. I also Don’t know how to Tweak the Function because I don’t understand the syntax of M to such a high level. Is there an Enterprise DNA course that I can look at to understand this. This is at a very high level for me.

Date.pbix (198.9 KB)
Holidays.xlsx (15.4 KB)

Major (8.1 MB)

If your Holidays are in a table, right click the header and choose “add as new query” give it a logical name in camelcase so you can easily refer to that query. Make sure they are integers.

You just need to replace this section, with the one I provided earlier:

    // List of days without saturdays and sundays
    ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),


Go to the new learning platform, enter my name in the search box
2 structured courses will show, start with the fundamentals :+1:

Hi Melissa,

So I figured out how to Tweak the functions, but it still is not working. How does one account for a Saturday where the working hours are only from 8:00 - 12:00 on a Saturday. Should I just do this in a column and use a conditional column. I don’t really need to do a whole course. My Power Query is good, it is just not strong with regards to functions. Please see attached files.
Holidays.xlsx (15.4 KB)
Major (8.1 MB)
Date.pbix (198.9 KB)

Hi @SammiP,

I’ll have a look later today.

It has taken a bit longer to get around to this, sorry for that.
Will pick this up as soon as I can.

1 Like