difference between dates in seconds excluding holiday list

Hi giovannafnr88,

The solution is not working for you because it would never work in a real-life scenario with that complexity.

If you take a look at the function, for every row, it creates a table of TotalSeconds rows. To put this into numbers, if your startdate and enddate are just two weeks apart, it will need work over 1.209.600 rows to calculate the time difference, and if they are 4 months apart, it will work over 10.368.000 rows. And this is per row.

With your mentioned table size, and take as a minimum one day difference, you'd need to work over a minimum of 30.499.200.000 rows. If this computes, you've got a great machine there.

Anyway, anything you try will be a bit heavy, but not like that. You can take the calculations down a lot.

1) holiday_list

Let's suppose for now that your holiday_list has days and not datetimes, You should add weekends to your holidays table if needed:

    //GetWeekends
    FirstDate = #date(2015,1,1),
    LastDate = DateTime.Date(DateTime.LocalNow()),    
    dur = Duration.TotalDays(LastDate-FirstDate),
    Cal = List.Dates(FirstDate, dur, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(Cal, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Weekends = Table.SelectRows(Table.TransformColumnTypes(DateTable,{{"Date", type date}}), each Date.DayOfWeek([Date],Day.Monday)>=5),

    //AddWeekends
    AllHolidays = Table.Combine({PreviousStep, Weekends}),
    Distinct = Table.Distinct(AllHolidays)
in
    Distinct

Where PreviousStep is your previous Holiday_list step,

Note here that I use 01/01/2015 as starting day and today as ending day on the calendar. You could change that if you wish. You could also make it dynamic by using FirstDate = DateTime.Date(List.Min(Table2[startdate])), LastDate = DateTime.Date(List.Max(Table2[enddate])), where 'tb' is your data table, but then you'd need to do 3) on a separate query.

2) holiday_count

(st as datetime, et as datetime) =>
    let
        s=DateTime.Date(st),
        e=DateTime.Date(et),
        dates = List.Select(holiday_list[Date], each _>=s and _<=e),
        datecount = List.Transform(dates, each
            if _=s
                then Duration.TotalDays((_ & #time(23,59,59))-st + #duration(0,0,0,1))
            else
                if _=e
                    then Duration.TotalDays(et - (_ & #time(0,0,0)))
                else 1),
        datesum = if s=e then 0 else List.Sum(datecount),
        res = Number.Round( Duration.TotalSeconds(et - st) - 86400*datesum, 0)
    in
        res

3) Table2

    #"Duration in Seconds" = Table.AddColumn(PreviousStep, "Duration", each holiday_count([startdate], [enddate]), Int64.Type)

Now, supposing that your holidays are not complete days or you also have something like "it's holiday from 00:00 to 08:00 and 16:00 to 00:00", then I'd need some dummy data to be able to give you something working.

Cheers,

Spyros