Net work hours help

[Sublimized]

   #"CSS Requests" = let


    Source = Excel.Workbook(Web.Contents("link"), null, true),
    #"CSS Requests_Sheet" = Source{[Item="Requests",Kind="Sheet"]}[Data],
 
    #"Promoted Headers" = Table.PromoteHeaders(#"CSS Requests_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LookupID", type text},  {"First Name", type text}, {"Last Name", type text}, {"Title", type text}, {"Specialists ", type any}, {"Member", type any}, {"Amended Member", type any}, {"Reason for Request", type text}, {"Div", type text}, {"Facility", type text}, {"Effective Date", type date}, {"Specialty", type text}, {"Created By", type text}, {"Additional Facilities", type text}, {"Call", type text}, {"Is License Requested", type text}, {"Entity", type text}, {"Rate 1", type number}, {"Rate 2", type number}, {"Rate 3", Int64.Type}, {"Rate Type1", type text}, {"Sign-on", type logical}, {"Stipend", type logical}, {"Job Requisition", type any}, {"Modified By", type text}, {"Special Notes", type text}, {"Additional Rates Comments", type text}, {"Status", type text}, {"State", type text}, {"Additional Entities", type text}, {"Division Valley", type text}, {"CSHUB WF", Int64.Type}, {"WF-TKN", type text}, {"AssignedToHubMember", Int64.Type}, {"CSHub Process Status", type text}, {"Item Type", type text}, {"Path", type text}, {"Member", type text},{"Created", type datetime},{"Modified", type datetime}}), 
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Created", "Reason for Request", "Status", "AssignedToMember", " Process Status", "Modified"}),
    WHStart=Number.From(WHSTART),
    WHEnd=Number.From(WHEND),

(WHStart, WHEnd, Start, End, ListOfHolidays) =>

let

    DStart             = Number.From(DateTime.Date(Start)), // start day
    TStart             = Number.From(DateTime.Time(Start)), // start time
    DEnd               = Number.From(DateTime.Date(End)),  // end day
    TEnd               = Number.From(DateTime.Time(End)),  // end time
    // List of days without saturdays and sundays
    ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
    // List of dates without holidays, saturdays and sundays 
    ListOfWorkingDays  = List.Difference(ListOfNumbers,ListOfHolidays),
    SumOfWorkingHours  = if DStart = DEnd then //checking if the start day is the same as the final day
                            if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
                               List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
                            else
                               0
                         else
                            (
                             if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
                                WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
                             else
                                0
                            )
                            +
                            (
                             if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
                                List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
                             else
                                0
                            )
                            +
                      // sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
                            List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart)
in
    SumOfWorkingHours

These above codes are my main query and my formula to find net work hours. i have set parameters at 9:00:00 and 17:00:00. My issue that im facing are that WHStart and WHEnd are not being recognized but they have been defined in the main query. Any help would be appreciated!

[v-frfei-msft]

Hi @Sublimized,

Please try to change your parameters like this. Also you can replace text to date /list, that depends on you.

(WHStart as text, WHEnd as text, Start as text, End as text, ListOfHolidays as text) as table =>

Regards,

Frank

[v-frfei-msft]

Hi @Sublimized,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[Sublimized]

Still trying and this is what i have so far. Still giving me the same issue

(WHStart, WHEnd, Start, End, ListOfHolidays) =>

let

    DStart             = Number.From(DateTime.Date(Start)), // start day
    TStart             = Number.From(DateTime.Time(Start)), // start time
    DEnd               = Number.From(DateTime.Date(End)),  // end day
    TEnd               = Number.From(DateTime.Time(End)),  // end time
    // List of days without saturdays and sundays
    ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
    // List of dates without holidays, saturdays and sundays 
    ListOfWorkingDays  = List.Difference(ListOfNumbers,ListOfHolidays),
    SumOfWorkingHours  = if DStart = DEnd then //checking if the start day is the same as the final day
                            if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
                               List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
                            else
                               0
                         else
                            (
                             if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
                                WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
                             else
                                0
                            )
                            +
                            (
                             if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
                                List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
                             else
                                0
                            )
                            +
                      // sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
                            List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart)
in
    SumOfWorkingHours

let
    #"Requests" = let


    Source = Excel.Workbook(Web.Contents("link" = Source{[Item="Requests",Kind="Sheet"]}[Data],
 
    #"Promoted Headers" = Table.PromoteHeaders(#"CSS Requests_Sheet", [PromoteAllScalars=true]),", type text}, {"Is License Requested", type text}, {"PrimaryEntity", type text}, {"Rate 1", type number}, {"Rate 2", type number}, {"Rate 3", Int64.Type}, {"Rate Type1", type text}, {"Sign-on Bonus", type logical}, {"Stipend", type logical}, {"Job Requisition", type any}, {"Modified By", type text}, {"Special Notes", t
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LookupID", type text},  {"First Name", type text}, {"Last Name", type text}, {"Title", type text}, {"Specialist ", type any}, {"Member", type any}, {"AmendedMember", type any}, {"Reason for Request", type text}, {"Div", type text}, {"Facility", type text}, {"Effective Date", type date}, {"Spel Facilities",cialty", type text}, {"Created By", type text}, {"Additiona type text}, {"Callype text}, {"Additional Rates Comments", type text}, {"Status", type text}, {"State", type text}, {"Additional Entities", type text}, {"Div", type text}, {"CSHUB WF", Int64.Type}, {"WF-TKN", type text}, {"AssignedToHubMember", Int64.Type}, {"Process Status", type text}, {"Item Type", type text}, {"Path", type text}, {"Member", type text},{"Created", type datetime},{"Modified", type datetime}}), 
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Created", "Reason for Request", "Status", "AssignedToHubMember", "CSHub Process Status", "Modified"}),
    WHStart=Number.From(WHSTART),
    WHEnd=Number.From(WHEND),
 #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each true),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNMonths([Modified], 1)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Modified", Order.Ascending}}),
    #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows", each ([Process Status] = "Approved" or [Process Status] = "Reject")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows2",{"Created", "Reason for Request", "Status", "Assigned", "Process Status", "Modified"}),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Other Columns1", each true)

in    #"Changed Type",
    #"Removed Other Columns" = Table.SelectColumns(#"CSS Requests",{"Created", "First Name", "Last Name", "Title", "Member", "AmendedMember", "Reason for Request", "Div", "Created By", "Modified By", "Status", "Assigned", "Process Status", "Member", "Modified"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Created", type datetime}}, "en-US"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Modified", type datetime}}, "en-US"),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type with Locale1", "Work Hours", each #"Work Hours"(WHStart, WHEnd, [Created], [Modified], Holidays))
in
    #"Invoked Custom Function"

[Sublimized]

This is my error