Excel BI Power Query Challenge 62 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Retype = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddDayName = Table.AddColumn(Retype, "DayName", each Date.DayOfWeekName( [Date] )),
    Filter = Table.RemoveColumns( Table.SelectRows(AddDayName, each ([DayName] <> "Sunday" and [DayName] <> "Saturday")), "DayName"),
    Group = Table.Group(Filter, {"Emp ID"}, {{"All", each _, type table [Emp ID=number, Date=nullable date]}}),
    AddIndex0 = Table.AddColumn(Group, "Index0", each Table.AddIndexColumn( [All], "Index0", 0, 1)),
    AddIndex1 = Table.RemoveColumns( Table.AddColumn(AddIndex0, "Index1", each Table.AddIndexColumn([Index0], "Index1", 1, 1)), {"All", "Index0"}),
    Expand = Table.ExpandTableColumn(AddIndex1, "Index1", {"Date", "Index0", "Index1"}, {"Date", "Index0", "Index1.1"}),
    Expand2 = Table.TransformColumnNames(Expand, Text.Trim, [MaxLength = 3]),
    Join = Table.Buffer( Table.SelectColumns( Table.Sort( Table.Join( Expand, {"Emp ID", "Index0"}, Expand2, {"Emp", "In1"}, JoinKind.LeftOuter ), {{"Emp ID", Order.Ascending}, { "Date", Order.Ascending}}), {"Emp ID", "Date", "Dat"})),
    RenamePrev = Table.RenameColumns(Join,{{"Dat", "PrevDate"}}),
    AddConsec = Table.AddColumn(RenamePrev, "Consecutive", each if Number.From( [Date] ) - Number.From( [PrevDate] ) = 1 then 1 else
if [PrevDate] = null then 1 else 
if Number.From( [Date] ) - Number.From( [PrevDate] ) = 3 and Date.DayOfWeekName( [Date] ) = "Monday" then 1 else 0),
    Group2 = Table.Group(AddConsec, {"Emp ID", "Consecutive"}, {{"FrmDate", each List.Min([PrevDate]), type date}, {"ToDate", each List.Max([Date]), type date}}, GroupKind.Local),
    AddFromDate = Table.RenameColumns( Table.RemoveColumns( Table.AddColumn(Group2, "FromDate", each if [Consecutive] = 1 then [FrmDate] else [ToDate]), {"Consecutive", "FrmDate"}), {"Emp ID", "Emp IDx"}),
    Join2 = Table.RemoveColumns( Table.Join( Table.Distinct( Table.SelectColumns( Source, "Emp ID")), "Emp ID", AddFromDate, "Emp IDx", JoinKind.LeftOuter), "Emp IDx"),
    ReplaceNulls = Table.Buffer( Table.Sort( Table.ReplaceValue(Join2,null,"NA",Replacer.ReplaceValue,{"ToDate", "FromDate"}), {{"Emp ID", Order.Ascending},{"FromDate", Order.Ascending}, {"ToDate", Order.Descending}})),
    DeDupe = Table.Distinct(ReplaceNulls, {"FromDate"}),
    Reorder = Table.ReorderColumns(DeDupe,{"Emp ID", "FromDate", "ToDate"}),
    AddTotWorkDays = Table.AddColumn(Reorder, "Total Work Days", each if [FromDate] = "NA" then 0 else 
if Date.DayOfWeekName([FromDate] ) <> "Friday" then
Number.From( [ToDate] ) - Number.From( [FromDate] ) + 1 else 
Number.From( [ToDate] ) - Number.From( [FromDate] ) - 1)
in
    AddTotWorkDays