Forum Discussion

SACooper's avatar
SACooper
Icon for Helper II rankHelper II
2 years ago
Solved

Cleaning non-helpful date formatting

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..

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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"], " " )
    )
)

     

     

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      SACooper
      Icon for Helper II rankHelper II
      2 years ago

      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!!