Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SACooper
Helper II
Helper II

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1698943900392.png

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1698943900392.png

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.