Duration Excluding exception, holidays and non-working work week

Hi San_Raz, are you sure that you've provided correct expected result?

This is my output

based on this logic:

• Duration = based on STANDARD WORK WEEK TABLE only
• Net Duration = based on STANDARD WORK WEEK TABLE and EXCEPTIONS TABLE

You have to replace these 3 tables with your table references (if you don't know how - read Note below my post)

let
    WeekStd = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lEKLs1LSawEMsLzi7KBlJmVgQEQKTj6InECQBxDI6VYHagu33xUXYZGyNoMDa1MLeH6jEwQ+kJKU4vJ0hiempJHptaQjNIi8nS6FWWSpS84saS0CKLTLz8vvhyiWwGOwQqNkAMfqEw3HKcyckLbiNzQNiI/tI3IDm0jMkPbCDW0idQZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calender ID" = _t, #"Standard Work Week" = _t, #"Work/Non_working" = _t, Start = _t, Finish = _t, #"Timeperiod (Hrs)" = _t]),
    Exceptions = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lEyMNY1MNE1MgEyw1NT8lKLUxIrgWzXiuTUgpLM/Dwg28zKwACIFBx9kTgBII6hkVKsDswgc7hBwaV5EFM88nMyISwFKDaAaDACMS3gGnzzMTRAEFB5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calender ID" = _t, Date = _t, #"Work Week" = _t, #"Holiday/Exception" = _t, Start = _t, End = _t, #"Timeperiod (Hrs)" = _t]),
    WeekStdChT = Table.RenameColumns(Table.TransformColumnTypes(WeekStd,{{"Start", type time}, {"Finish", type time}}), {{"Calender ID", "Calendar ID"}}, MissingField.Ignore),
    ExceptionsChT = Table.RenameColumns(Table.TransformColumnTypes(Exceptions,{{"Date", type date}, {"Start", type time}, {"End", type time}}), {{"Calender ID", "Calendar ID"}}, MissingField.Ignore),
    H = [ fn_ReplaceNullTimes = (tbl, startColName, endColName)=>  Table.TransformColumns(tbl, {{startColName, each if _ = null then #time(0,0,0) else _, type time}, {endColName, each if _ = null then #time(0,0,0) else _, type time}}), //Replace null times to 0:00:00 time
    WeekStd = Table.Buffer(fn_ReplaceNullTimes(Table.SelectColumns(WeekStdChT,{"Calendar ID", "Standard Work Week", "Start", "Finish"}), "Start", "Finish")),
    Exceptions = Table.Buffer(fn_ReplaceNullTimes(Table.SelectColumns(ExceptionsChT,{"Calendar ID", "Date", "Start", "End"}), "Start", "End")) ],
    SummaryTbl = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI2BBIGxroGJrpGJgoWVgYGIL4FlG9oABKI1YlWcgKpNSJCbSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activty = _t, #"Calnder ID" = _t, #"Start Date" = _t, #"End date" = _t]),
    SummaryTblChT = Table.RenameColumns(Table.TransformColumnTypes(SummaryTbl,{{"Start Date", type datetime}, {"End date", type datetime}}), {{"End date", "End Date"}, {"Calnder ID", "Calendar ID"}, {"Calender ID", "Calendar ID"}}, MissingField.Ignore),
    Ad_helper = Table.AddColumn(SummaryTblChT, "helper", each 
        [ sDate = Date.From([Start Date]),
          eDate = Date.From([End Date]),
          dates = Table.AddColumn(Table.FromList(List.Dates(sDate, Duration.TotalDays(eDate-sDate)+1, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[ActDate=date]), "ActCalendar ID", (x)=> [Calendar ID], type text),
          mergeExceptions = Table.ExpandTableColumn(Table.NestedJoin(dates, {"ActDate", "ActCalendar ID"}, H[Exceptions], {"Date", "Calendar ID"}, "Exceptions", JoinKind.LeftOuter), "Exceptions", {"Start", "End"}, {"ExceptionStart", "ExceptionEnd"}),
          Ad_DayName = Table.AddColumn(mergeExceptions, "DayName", (x)=> Date.DayOfWeekName(x[ActDate], "en-US")),
          mergeWeekStd = Table.ExpandTableColumn(Table.NestedJoin(Ad_DayName, {"DayName", "ActCalendar ID"}, H[WeekStd], {"Standard Work Week", "Calendar ID"}, "WeekStd", JoinKind.LeftOuter), "WeekStd", {"Start", "Finish"}, {"WeekStdStart", "WeekStdEnd"}),
          Ad_NetStart = Table.AddColumn(mergeWeekStd, "DurationNetStart", (x)=> if x[ActDate] = sDate then List.Max({x[ExceptionStart], Time.From([Start Date])}) else List.First(List.RemoveNulls({x[ExceptionStart], x[WeekStdStart]})), type datetime),
          Ad_NetEnd = Table.AddColumn(Ad_NetStart, "DurationNetEnd", (x)=> if x[ActDate] = eDate then List.Min({x[ExceptionEnd], Time.From([End Date])}) else List.First(List.RemoveNulls({x[ExceptionEnd], x[WeekStdEnd]})),type datetime),
          NetDuration = Table.AddColumn(Ad_NetEnd, "NetDuration", (x)=> let dur = Duration.TotalHours(x[DurationNetEnd]-x[DurationNetStart]) in if Time.Minute(x[DurationNetEnd]) = 59 then Number.RoundUp(dur, 0) else dur),
          Ad_Start = Table.AddColumn(NetDuration, "DurationStart", (x)=> if x[ActDate] = sDate then Time.From([Start Date]) else x[WeekStdStart], type datetime),
          Ad_End = Table.AddColumn(Ad_Start, "DurationEnd", (x)=> if x[ActDate] = eDate then Time.From([End Date]) else x[WeekStdEnd], type datetime),
          DurationTbl = /* Table.Sort( */Table.AddColumn(Ad_End, "Duration", (x)=> let dur = Duration.TotalHours(x[DurationEnd]-x[DurationStart]) in if Time.Minute(x[DurationEnd]) = 59 then Number.RoundUp(dur, 0) else dur)/* , {{"ActDate", Order.Ascending}}) */,
          Result = [ NetDuration = List.Sum(DurationTbl[NetDuration]), Duration = List.Sum(DurationTbl[Duration]) ]
        ], type record ),
    ExpandedHelper = Table.ExpandRecordColumn(Ad_helper, "helper", {"DurationTbl", "Result"}, {"DurationTbl", "Result"}),
    ExpandedResult = Table.ExpandRecordColumn(ExpandedHelper, "Result", {"NetDuration", "Duration"}, {"NetDuration", "Duration"})
in
    ExpandedResult