cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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)