Cleaning non-helpful date formatting

[SACooper]

I have a date source which is very unhelpful in that it produces date/time fields in text as

Today at 11:15 am, yesterday at 2:46 pm, Tuesday at 3:21 pm and so on until we are a week out then we start getting more helpful date and time stamps.

I played around using the created date of the extract to help calculate "today" by doing:

Table.ReplaceValue(#"Removed Duplicates","today at",each Date.ToText([Date created]),Replacer.ReplaceText,{"Date/Time"})

and for yesterday by doing:

Table.ReplaceValue(#"Replaced Value","yesterday at", each Date.ToText(Date.AddDays([Date created],-1)),Replacer.ReplaceText,{"Date/Time"})

but now unsure how to change "Monday/Tuesday/Wednesday/Thursday etc at" to a date..

[AlexisOlson · Accepted solution]

You could check which day in the next week has a matching day-of-week name. 

For example, add a custom column like this:

List.First(
    List.Select(
        List.Dates(
            Date.AddDays([Date created], 1),
            7,
            #duration(1,0,0,0)
        ),
        (d) => Date.DayOfWeekName(d)
               = Text.BeforeDelimiter([#"Date/Time"], " " )
    )
)

[SACooper]

Thank you -- this is amazing and was the key to solving the problem had to do a bit of date math after but was simple enough and got me where I needed to be!!