Excel BI Excel Challenge 105 – Brian Julius Solution

gistfile1.txt

(#"Input Number of Years" as number) as table =>
let
    Source = 
    [
    Today = DateTime.FixedLocalNow(),
    CurrYear = Date.Year( Today ),
    EndYear = if Number.From( #date( CurrYear, 12, 25)) > Number.From( Today ) then CurrYear - 1 else CurrYear,
    StartYear = EndYear - #"Input Number of Years" + 1,
    StartDate = #date(StartYear, 12, 25),
    EndDate =  #date(EndYear, 12, 25),
    DateList = { Number.From( StartDate )..Number.From( EndDate ) }
    ][DateList], 
    DateTable = Table.TransformColumnTypes( Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), {"Column1", type date}),
    DayName3 = Table.AddColumn(DateTable, "Day of Week", each Text.Start( Date.DayOfWeekName([Column1]), 3)),
    Filter = Table.AddColumn( Table.SelectRows(DayName3, each (Date.Month([Column1]) = 12 and Date.Day([Column1]) = 25)), "Day", each Date.DayOfWeek( [Column1]) ),
    Group = Table.RenameColumns( Table.RemoveColumns( Table.Sort( Table.Group(Filter, {"Day of Week", "Day"}, {{"Count", each Table.RowCount(_), Int64.Type}}), {"Day", Order.Ascending}), "Day"), {"Day of Week", "Day"})
in
    Group