Need a M function to get the proper date

Makes sense, I would probably approach it slightly differently.

Here's the code, paste this into the advanced editor, alternately, Excel file is attached.

let
    Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
    CT1 = Table.TransformColumnTypes(Source, {{"Column1", type date}}),
    Rename1 = Table.RenameColumns(CT1, {{"Column1", "Date"}}),
    InsertStartOfWeek = Table.AddColumn(
        Rename1, "Start of Week", each Date.StartOfWeek([Date]), type date
    ),
    InsertEndOfWeek = Table.AddColumn(
        InsertStartOfWeek, "End of Week", each Date.EndOfWeek([Date]), type date
    ),
    AddDateList = Table.AddColumn(
        InsertEndOfWeek, "DateList", each {Number.From([Start of Week])..Number.From([End of Week])}
    ),
    ExpandDateList = Table.ExpandListColumn(AddDateList, "DateList"),
    CT2 = Table.TransformColumnTypes(ExpandDateList, {{"DateList", type date}}),
    InsertDayName = Table.AddColumn(
        CT2, "Day Name", each Date.DayOfWeekName([DateList]), type text
    ),
    ROC1 = Table.SelectColumns(InsertDayName, {"DateList", "Day Name"}),
    Rename2 = Table.RenameColumns(ROC1, {{"DateList", "Date"}})
in
    Rename2