Need help on power query

[PowerBigginer]

Hello Team,

I’m currently facing challenges in Power Query while trying to calculate the difference in days between two dates, considering only working days, working hours, and excluding holidays.

Although I have a DAX-based solution that works as expected in terms of logic, it has performance issues. When attempting the same in Power Query, I'm encountering errors, especially for tickets created outside of working hours.

I would appreciate your help in resolving this.
Please find below my DAX and Power Query scripts for reference.

Thank you.

let
// Load data
Source = Sql.Database("RV2IVWPBI1P\RAYAMSSQLSERVER", "RayaOperationsData"),
SR = Source{[Schema="dbo", Item="Fact_Siebel_ServiceRequest"]}[Data],

// Remove unwanted columns
RemovedCols = Table.RemoveColumns(SR, {
"SR_TYPE", "SUB_AREA", "PRIORITY", "REQUESTOR_NAME_EN", "SEVERITY",
"SR_DUE_DATE", "TOTAL_TIME", "VEHICLE_MAKE", "VEHICLE_MODEL", "MODEL_YEAR",
"PENDING_PAYMENT_START", "PENDING_PAYMENT_END", "LAST_UPD", "dw_created", "dw_last_upd"
}),

ChangedTypes = Table.TransformColumnTypes(RemovedCols, {
{"SR_CREATED", type datetime},
{"SR_CLOSE_DATE", type datetime}
}),

// Define working hours
WorkingStart = #time(8, 0, 0),
WorkingEnd = #time(17, 0, 0),

// 🟨 Holiday List
HolidayList = {
#date(2025, 3, 30), #date(2025, 3, 31), #date(2025, 4, 1), #date(2025, 4, 2),
#date(2025, 6, 5), #date(2025, 6, 8), #date(2025, 6, 9), #date(2025, 6, 10)
},

// ✅ Adjust Created Date
AdjustedStart = Table.AddColumn(ChangedTypes, "AdjustedStart", each
let
dt = [SR_CREATED],
defaultTime = #time(8, 0, 0),
safeDT = if dt = null then DateTime.LocalNow() else dt,
t = Time.From(safeDT),
d = Date.From(safeDT),
dow = Date.DayOfWeek(d, Day.Sunday),
nextWorkDate =
List.First(
List.Select(
List.Dates(d + 1, 7, #duration(1,0,0,0)),
each Date.DayOfWeek(_, Day.Sunday) <= 4 and not List.Contains(HolidayList, _)
)
),
adjusted =
if dow > 4 or List.Contains(HolidayList, d) then
DateTime.From(nextWorkDate + defaultTime)
else if t < defaultTime then
DateTime.From(d + defaultTime)
else if t >= WorkingEnd then
DateTime.From(nextWorkDate + defaultTime)
else safeDT
in
adjusted
, type datetime),

// ✅ Adjust Closed Date
AdjustedEnd = Table.AddColumn(AdjustedStart, "AdjustedEnd", each
let
dt = if [SR_CLOSE_DATE] = null then DateTime.LocalNow() else [SR_CLOSE_DATE],
t = Time.From(dt),
d = Date.From(dt),
adjusted =
if t < WorkingStart then
DateTime.From(d + WorkingStart)
else if t > WorkingEnd then
DateTime.From(d + WorkingEnd)
else dt
in
adjusted
, type datetime),

// Date Range
AddDateRange = Table.AddColumn(AdjustedEnd, "DateRange", each
let
s = Date.From([AdjustedStart]),
e = Date.From([AdjustedEnd])
in
List.Dates(s, Duration.Days(e - s) + 1, #duration(1, 0, 0, 0))
, type list),

// Working Days (Sunday–Thursday, excluding holidays)
WorkingDates = Table.AddColumn(AddDateRange, "WorkingDates", each
List.Select([DateRange], each Date.DayOfWeek(_, Day.Sunday) <= 4 and not List.Contains(HolidayList, _))
, type list),

// Calculate Working Hours
WorkingHours = Table.AddColumn(WorkingDates, "WorkingHours", each
let
startDT = [AdjustedStart],
endDT = [AdjustedEnd],
startDate = Date.From(startDT),
endDate = Date.From(endDT),
dates = [WorkingDates],
hours = List.Sum(
List.Transform(dates, each
let
currentDate = _,
startTime = if currentDate = startDate then Time.From(startDT) else WorkingStart,
endTime = if currentDate = endDate then Time.From(endDT) else WorkingEnd,
validStart = if startTime < WorkingStart then WorkingStart else startTime,
validEnd = if endTime > WorkingEnd then WorkingEnd else endTime,
duration = Duration.TotalHours(validEnd - validStart)
in
if validEnd > validStart then duration else 0
)
)
in
if startDT = null or endDT = null then null else Number.Round(hours, 2)
, type number),

// Calculate SLA Working Days (9 hours per day rule)
SLA_WorkingDays = Table.AddColumn(WorkingHours, "SLA_WorkingDays", each
let
wh = [WorkingHours]
in
if wh = null then null
else if wh < 9 then 0
else Number.RoundUp(wh / 9)
, type number),

Cleanup = Table.RemoveColumns(SLA_WorkingDays, {"DateRange", "WorkingDates"})
in
Cleanup

[v-tsaipranay]

Hi @PowerBigginer ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @PowerBigginer ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay]

Hi @PowerBigginer ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

To fully meet your need (working days + partial working hours + holiday/weekend exclusions), you can combine @jgeddes  logic for day counting with @wardy912  adjustments for edge cases and extend the calculation to include working hours.

Thank you.

[wardy912]

Hi @PowerBigginer 

 A couple of edits:

Instead of skipping to the next working day immediately, include the current day if it's a working day, even if the time is outside working hours. This ensures the current day is still considered in the DateRange.

adjusted =
if dow > 4 or List.Contains(HolidayList, d) then
    DateTime.From(nextWorkDate + defaultTime)
else if t < defaultTime then
    DateTime.From(d + defaultTime)
else if t >= WorkingEnd then
    DateTime.From(d + defaultTime)  // Stay on same day, but move to next morning
else
    safeDT

In WorkingHours, add a check to ensure WorkingDates is not empty before proceeding:

hours = if List.Count(dates) = 0 then null else List.Sum(
    List.Transform(dates, each
        let
            currentDate = _,
            startTime = if currentDate = startDate then Time.From(startDT) else WorkingStart,
            endTime = if currentDate = endDate then Time.From(endDT) else WorkingEnd,
            validStart = if startTime < WorkingStart then WorkingStart else startTime,
            validEnd = if endTime > WorkingEnd then WorkingEnd else endTime,
            duration = Duration.TotalHours(validEnd - validStart)
        in
            if validEnd > validStart then duration else 0
    )
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[jgeddes · Accepted solution]

Here is a methodology you may be able to apply to your case.

A brief explanation.

Set a list of holiday names. 

Set the working start and end times.

Determine the day of the week of the created datetime. (Using Day.Monday makes Monday day 0)

Compare the created time of the created datetime to the working end time. If the created time is after the working end time then advance the created date by one day.

Now look at the day of the week of the amended created date. If it is Saturday or Sunday then advance the created day to the next Monday. (Call this the effective start date.)

Create a list of dates from the effective start date to and including the close date.

Remove any days from the holiday list from the created date list and then remove any remaining days that are Saturdays or Sundays from the list.

Count the number of rows left in the list and that should be the number of working days.
Hope this gets you pointed in the right direction.

let
    HolidayList = {
        #date(2025, 3, 30), #date(2025, 3, 31), #date(2025, 4, 1), #date(2025, 4, 2),
        #date(2025, 6, 5), #date(2025, 6, 8), #date(2025, 6, 9), #date(2025, 6, 10)
    },
    WorkingStart = #time(8, 0, 0),
    WorkingEnd = #time(17, 0, 0),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcpLCsAwCAXAqxTXAT8vaRKvIt7/Gq3gdpgIErAuNrH1yHUsGjS5QV/HphxBYGiblVXaDeKwTmpt12elX06LOg5lfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Created_DateTime = _t, Close_DateTime = _t]),
    set_types = 
    Table.TransformColumnTypes(
        Source,
        {{"Created_DateTime", type datetime}, {"Close_DateTime", type datetime}}
    ),
    workingDayCount = 
    Table.AddColumn(
        set_types, 
        "WorkingDuration", 
        each 
        let
            dayOfWeek =             //Assuming workdays are Monday to Friday, this sets Monday as day 0
            Date.DayOfWeek(
                [Created_DateTime], 
                Day.Monday
            ),
            workEndDate =           //if the created time is after working hours the created date is advanced to the next day.
            if Time.From([Created_DateTime]) > WorkingEnd 
                then Date.From(Date.AddDays([Created_DateTime], 1)) 
                else Date.From([Created_DateTime]
            ),
            effectiveStartDate =    //if the workEndDate is after Friday (day 4 in a zero index list) the date is advanced by the number of days required to get to the next Monday.
            if dayOfWeek > 4 
                then Date.AddDays(workEndDate, (7-dayOfWeek)) 
                else workEndDate,
            initialList =           //create a list of dates from the effective start date to and including the clost date.
            List.Dates(
                effectiveStartDate, 
                Number.Round(Number.From(Date.From([Close_DateTime]) - effectiveStartDate), 0) + 1, 
                #duration(1,0,0,0)
            ),
            dayDuration =           //remove the holiday dates and Saturdays and Sundays from the created list and then count the number rows in the list
            List.Count(
                List.Select(
                    List.Difference(initialList, HolidayList), 
                    each Date.DayOfWeek(_, Day.Monday) <> 5 and Date.DayOfWeek(_, Day.Monday) <> 6
                )
            )
        in
            dayDuration,
        type number
    )
in
    workingDayCount