Forum Discussion
oliverblane
Helper III
4 years agoOptimise 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...
- 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
AlexisOlson
Super User
4 years agoThe 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
- oliverblane4 years ago
Helper III
This worked perfectly, thank you!