Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
#"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!
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
Hi @Sublimized,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
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"
This is my error
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |