let
Source = #table(type table [Date = date], List.Zip({List.Dates(#date(2018,1,1),730,#duration(1,0,0,0))})),
#"Inserted Year" = Table.AddColumn(Source, "Год", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Месяц", each Date.Month([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "День недели", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
// календарь сделали
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([День недели] = 4)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Год", "Месяц", "День недели"}, {{"tab", each Table.SelectRows(Table.AddIndexColumn(_[[Date]],"i"), each [i]=2), type table [Date=date, i = number]}}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "tab", {"Date"}, {"Friday"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded {0}",{"Friday"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Monday", each [Friday]+#duration(3,0,0,0), type date),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Friday"}, Document, {"Дата"}, "Document", JoinKind.LeftOuter),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries", "Document", {"Статус"}, {"Выходные пт"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded {0}1", {"Monday"}, Document, {"Дата"}, "Document", JoinKind.LeftOuter),
#"Expanded {0}2" = Table.ExpandTableColumn(#"Merged Queries1", "Document", {"Статус"}, {"Выходные пн"}),
#"Added Custom1" = Table.AddColumn(#"Expanded {0}2", "3rd Friday", each if [Выходные пт]=null then [Friday] else [Friday]-#duration(1,0,0,0), type date),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Next Monday", each if [Выходные пн]=null then [Monday] else [Monday]+#duration(1,0,0,0), type date),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"3rd Friday", "Next Monday"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns1",{{"3rd Friday", Order.Ascending}})
in
#"Sorted Rows" |
