Forum Discussion

oliverblane's avatar
oliverblane
Icon for Helper III rankHelper III
4 years ago
Solved

Optimise Query for Calculating Number of Business Days

I have a table "DateTable" which has two columns: the first is every date between a start date (1st March 2019) and the current date the second is the number of business days that have occurred be...
  • AlexisOlson's avatar
    4 years ago

    The key to performance is to only calculate this column once instead of once for each row of your date table.

    My approach would be to add an index column after filtering out weekends and holidays and then merge this back with the original dates.

     

    let
        StartDate = #date(2019, 3, 1),
        EndDate = Date.From(DateTime.LocalNow()),
        ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
        ListToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), {"Date"}),
        DateTable = Table.TransformColumnTypes(ListToTable, {{"Date", type date}}),
        RemoveDays = Table.SelectRows(DateTable, each Date.DayOfWeek([Date], 1) < 5 and
                                                      not List.Contains(#"bank-holidays-dl",
        AddIndex = Table.AddIndexColumn(RemoveDays, "Index", 1, 1, Int64.Type),
        MergeQueries = Table.NestedJoin(DateTable, {"Date"}, AddIndex, {"Date"}, "RemovedDays", JoinKind.LeftOuter),
        ExplandIndex = Table.ExpandTableColumn(MergeQueries, "RemovedDays", {"Index"}, {"WorkingDaysSinceStart"}),
        SortRows = Table.Sort(ExplandIndex,{{"Date", Order.Ascending}}),
        FillDown = Table.FillDown(SortRows, {"WorkingDaysSinceStart"})
    in
        FillDown