cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

Change sundays to mondays

ok with referals they come in all hours of the day. but we only work M-F with a few hours on Sat. so all the refureals on Sunday really dont get worked till monday. So I need to beable to push the ones that fall on Sun to the next day. So I can push all sunday referals to 8am no problem, but i need to also push the date from 3/20/2022 to 3/21/2022.

I do have a colum that has the day ot eh week so I can easly filter to only the sunday referals.

This is needs to be done in Power Pivot. i would try a If and statment but cant get it to work or maybe just bad formula.

= Table.AddColumn(#"Expanded Dates", "Custom", each if [Dates.Day of Wk]= "Sun" then [List Details.TIMESTAMP]+0/1/0 else [List Details.TIMESTAMP])

1 ACCEPTED SOLUTION
Community Support

What columns do you already have and what is the expected result?

According to the code you provided, it seems you want to add a column and add one day to dates falling on Sundays, right? If so, you can use Date.AddDays function.

``= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then Date.AddDays([Date],1) else [Date])``

Or use [Date]+#duration(1,0,0,0) to get the same result. #duration function

``= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then [Date]+#duration(1,0,0,0) else [Date])``

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

2 REPLIES 2
Community Support

What columns do you already have and what is the expected result?

According to the code you provided, it seems you want to add a column and add one day to dates falling on Sundays, right? If so, you can use Date.AddDays function.

``= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then Date.AddDays([Date],1) else [Date])``

Or use [Date]+#duration(1,0,0,0) to get the same result. #duration function

``= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then [Date]+#duration(1,0,0,0) else [Date])``

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Super User

@mattue84 , in the previous step, where you setup day of week you can use additional parameter to set start of week as monday

https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek

Date.DayOfWeek(#date(2011, 02, 21), Day.Monday)

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.