let
Source =
Table
.TransformColumnTypes
(
.FromRows({
{
"2017-01-01"
,
"New Year's Day"
},
"2017-01-02"
"New Year's Day - Observed"
"2017-01-16"
"Martin Luther King, Jr. Day"
"2017-02-15"
"Presidents' Day"
"2017-05-29"
"Memorial Day"
"2017-07-04"
"Independence Day"
"2017-09-04"
"Labor Day"
"2017-11-23"
"Thanksgiving Day"
"2017-11-24"
"Day after Thanksgiving"
"2017-12-25"
"Christmas Day"
}
"HolidayDate"
"HolidayName"
}),
{{
, type
date
}, {
, type text}}
)
in
Source
//
Set
range
DateBeg = DateTime.
Date
.StartOfMonth(
.AddMonths(DateTime.LocalNow(), 0))),
DateEnd = DateTime.
.EndOfMonth(DateTime.LocalNow())),
DurationDays = Duration.Days(Duration.
From
(DateEnd-DateBeg)) + 1,
// Generate list
of
dates
then
convert
to
typed
table
DateList = List.Dates(DateTime.
(DateBeg), DurationDays, #duration(1, 0, 0, 0)),
DateTable =
.FromList(DateList, Splitter.SplitByNothing(), {
"EventDate"
}, ExtraValues.Error),
DateTableTyped =
.TransformColumnTypes(DateTable, {{
}}),
// CalendarTable
is
consumed the Typed Data
; this short ETL line allows
for
// changing the dates
from
being computed
a calendar source
CalendarTable = DateTableTyped,
// Determine if
a weekend
AddIsWeekend =
.AddColumn(CalendarTable,
"IsWeekend"
, each if (
.DayOfWeek([EventDate],
Day
.Monday) >= 5)
1
else
0, Int32.Type),
a holiday
AddHolidayName =
.ExpandTableColumn(
.NestedJoin(AddIsWeekend, {
}, CalendarHolidays, {
, JoinKind.LeftOuter),
, {
AddIsHoliday =
.AddColumn(AddHolidayName,
"IsHoliday"
, each if([HolidayName]
null
0
1, Int32.Type),
a
work
day
not
weekend,
holiday)
AddIsWorkDay =
.AddColumn(AddIsHoliday,
"IsWorkDay"
, each if(([IsWeekend] + [IsHoliday]) = 0)
// Determine previous
fcnPrevWorkDate = (Calendar
as
, CalendarDate
=>
fcn = List.
Max
.SelectRows(Calendar, each ([IsWorkDay] = 1
and
[EventDate] <=
.
(CalendarDate)))[EventDate]),
fnz = List.
Min
[EventDate] >
ref = if (fcn <>
fcn
fnz
ref,
Add
previous
AddPrevWorkDate =
.AddColumn(AddIsWorkDay,
"PreviousWorkDate"
, each fcnPrevWorkDate(AddIsWorkDay, [EventDate]), type
AddPrevWorkDate