Excel BI Challenge 59 – Brian Julius Solution

gistfile1.txt

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMtT1SsxTitWJVjLR9SrNAbMMDXX98svATCNTXZfUZKXYWAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Holidays = _t]
  ), 
  DateType = Table.TransformColumnTypes(Source, {{"Holidays", type date}}), 
  SplitHolidays = Table.RemoveColumns(
    Table.RenameColumns(
      Table.SplitColumn(
        Table.TransformColumnTypes(DateType, {{"Holidays", type text}}, "en-US"), 
        "Holidays", 
        Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), 
        {"Holidays.1", "Holidays.2", "Holidays.3"}
      ), 
      {{"Holidays.1", "HolidayMonth"}, {"Holidays.2", "HolidayDay"}}
    ), 
    "Holidays.3"
  ), 
  HolidayTable = Table.TransformColumnTypes(
    SplitHolidays, 
    {{"HolidayMonth", Int64.Type}, {"HolidayDay", Int64.Type}}
  ), 
  DateList = Table.RenameColumns(
    Table.TransformColumnTypes(
      Table.FromList(
        {Number.From(#date(2000, 1, 1)) .. Number.From(#date(2099, 12, 31))}, 
        Splitter.SplitByNothing(), 
        null, 
        null, 
        ExtraValues.Error
      ), 
      {{"Column1", type date}}
    ), 
    {{"Column1", "Date"}}
  ), 
  Nonwork = Table.AddColumn(
    DateList, 
    "Nonwork", 
    each 
      if List.AllTrue(
        {
          List.Contains(HolidayTable[HolidayMonth], Date.Month([Date])), 
          List.Contains(HolidayTable[HolidayDay], Date.Day([Date]))
        }
      )
      then
        1
      else if Text.StartsWith(Date.DayOfWeekName([Date]), "S") then
        1
      else
        0
  ), 
  FilteredNonwork = Table.SelectRows(Nonwork, each ([Nonwork] = 0)), 
  InsertYear = Table.AddColumn(FilteredNonwork, "Year", each Date.Year([Date]), Int64.Type), 
  GroupCount = Table.Group(InsertYear, {"Year"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 
  GroupedWorkDays = Table.RemoveColumns(
    Table.ExpandTableColumn(
      Table.FirstN(
        Table.Sort(
          Table.Group(
            GroupCount, 
            {"Count"}, 
            {{"All", each _, type table [Year = number, Count = number]}}
          ), 
          {{"Count", Order.Descending}}
        ), 
        1
      ), 
      "All", 
      {"Year"}, 
      {"Year"}
    ), 
    "Count"
  )
in
  GroupedWorkDays