cancel
Showing results for
Did you mean:

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

Helper II

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
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(
7,
#duration(1,0,0,0)
),
(d) => Date.DayOfWeekName(d)
= Text.BeforeDelimiter([#"Date/Time"], " " )
)
)``````

2 REPLIES 2
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(
7,
#duration(1,0,0,0)
),
(d) => Date.DayOfWeekName(d)
= Text.BeforeDelimiter([#"Date/Time"], " " )
)
)``````

Helper II

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors