Forum Discussion
Power Query Time difference between two dates/times columns excluding holidays
Hello
I've now adapted the function. has a 3rd parameter that requires a list of days that are representing the holidays
(StartTime as datetime, EndTime as datetime, ListHoliday as nullable list) as number => let //StartTime = #datetime(2019,12,3,8,00,00), //EndTime = #datetime(2019, 12, 8, 8, 0, 0), //ListHoliday = {"03.12.19", "04.12.19"}, ListHolidayCheck = if ListHoliday = null then {} else ListHoliday, ListHolidayInternal = List.Transform(ListHolidayCheck, each Date.From(_)), DurationInHours = (Number.From(EndTime)-Number.From(StartTime))*24, DateTimesFromStartEnd = List.DateTimes(StartTime, DurationInHours, #duration(0,1,0,0)), FilterHolidays = List.Transform(DateTimesFromStartEnd, each if List.Contains(ListHolidayInternal, Date.From(_))= true then null else _), TableWithDateTime = #table({"DateTime"}, List.Zip({FilterHolidays})), #"Added Custom" = Table.AddColumn(TableWithDateTime, "Hours", each 1), #"Inserted Day of Week" = Table.AddColumn(#"Added Custom", "Day of Week", each Date.DayOfWeek([DateTime], Day.Monday), Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] < 5)), HoursSum = List.Sum(#"Filtered Rows"[Hours]) in HoursSumhave fun
Jimmy
Jimmy801 thank you for this, much appreciated. Will have a go at creating this into my query model and let you know if I have any issues. If no issues, I will mark this post as 'Accepted Solution'
Jimmy801 yes that worked with the following edit
=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99
Thank you for your help and patience, much appreciated:)
@Jimmy801 yes that worked with the following edit
=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99
Thank you for your help and patience, much appreciated:)
Sorry my bad. Ok so after pasting into Advanced Editor, the resulting Custom Column values shows "Error" and after clicking on Error, the following appears...
Hello Anonymous ,
try to invoke the function like this: =fxHoursBetweenDates([startdatetim], [endtdatetime], null)
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Jimmy801 I tried that but still getting Error. Are you able to replicate from your end?
- Hello
Seems to me as if you are feeding the function with a null value. Please share your file
Jimmy Yes some of the cells in the date time columns have 'No Scan' (in the raw data source file) but 'Null' in query editor as per screenshots of sample data below
Hello Anonymous
but these databases are not the same, aren't they?
You would need to invoke the funciton only when both columns are filled... meaning that no column contains null or "no scan"
Nevertheless, the initial request was fullfilled, the function works.
So please mark the right answer as solution
Have a nice day
Jimmy
Hi Jimmy801 the source data is in another Excel file that is pulled into my current Excel file via power query. I believe this is irrelevant as my exisiting custom column that calculates the time difference in hours (not exlcuding weekends/holidays) works fine i.e.
=try Number.RoundDown(Duration.Days(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME]))) * 24+ Duration.Hours(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME])) otherwise -99
Is there a way to exclude null from your function as the above function does?
Hey
what do you mean by excluding nulls?
Why don't invoke my function with try otherwise... so when wrong parameters are passed to the function you will get -99 in your custom columns?
BR
Jimmy
Jimmy801 yes that worked with the following edit
=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99
Thank you for your help and patience, much appreciated:)
Hello
and why did you mark your own post as solution, and not my function??
You should fix that
Jimmy
Jimmy801 amateur mistake, sorry. Fixed now. Thanks again.
@Jimmy801 yes that worked with the following edit
=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99
Thank you for your help and patience, much appreciated:)
Hi @Jimmy801 the source data is in another Excel file that is pulled into my current Excel file via power query. I believe this is irrelevant as my exisiting custom column that calculates the time difference in hours (not exlcuding weekends/holidays) works fine i.e.
=try Number.RoundDown(Duration.Days(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME]))) * 24+ Duration.Hours(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME])) otherwise -99
Is there a way to exclude null from your function as the above function does?