Power Query How to Turn any day into a Monday

[Anonymous]

Hello,

I have a column with a list of dates, about 20k records.

What I need to do is convert every date into the first Monday of the week.

4/7/2020 > 4/6/2020

4/12/2020 > 4/6/2020

4/13/2020 > 4/13/2020

4/23/2020 > 4/20/2020

Is there anyway to do this without having a huge lookup table for everyday of the week?  In Excel I can use something like:

(A2+1)-WEEKDAY((A2+1)-2)

[amitchandak]

This in DAX- This will week strat as Monday and Week-End as Sunday for all the dates between Monday and Sunday

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

[Anonymous · Accepted solution]

I was actually looking for a solution in M for PowerQuery, but your formula got me thinking.  Here is what I ended up with:

Date.AddDays([Date],-Date.DayOfWeek([Date],Day.Monday))

[Anonymous]

What I want to transform the dates column so if the date is Saturday, 07 Nov 2020 to become 09 Nov 2020, if the date is Sunday, 08 Nov 2020 to become 09 Nov 2020, else, to keep the date as it is? Any help please?

[Ashish_Mathur]

Hi,

This calculated column formula works

=Table1[Date]+SWITCH(WEEKDAY(Table1[Date],2),1,0,2,0,3,0,4,0,5,0,6,2,7,1)

Hope this helps.

[Anonymous]

Excuse my ignorace, is this DAX or M? As I'm searching for a solution of M Power Query. My knowledge is really limited too, so, may I ask you please to provide the formula for these entries: Table. [Date], thank you.

[v-xuding-msft]

Hi @Anonymous ,

Thanks for sharing your solution. It is working perfectly. Please accept the answer as a solution. Then more people who have the same requirement will benefit from the thread.