How to Group Dates to create a 'Week Start' column

[Anonymous]

Hello,

I'm trying to create a column in my calendar that says 'Week Start'. Essentially what I want it to say is 'If the day of the week is Monday, then take that date and copy it down till the next Monday'. And then when it gets to next Monday, take that new date and copy it down. Please let me know how I can do this in Power BI! Thanks!

Example:

Day of week     Date                           Week Start

M                      Dec 26 2022              Dec 26 2022

T                        Dec 27 2022              Dec 26 2022

W                     Dec 28 2022               Dec 26 2022

T                       Dec 29 2022              Dec 26 2022

F                       Dec 30 2022               Dec 26 2022

S                       Dec 31 2022              Dec 26 2022

S                       Jan 1 2022                 Dec 26 2022

M                     Jan 2 2022                  Jan 2 2022

T                       Jan 3 2022                  Jan 2 2022

[Anonymous]

Just use a calculated column that tells you the Monday of the date in question

 Date.StartOfWeek([Date], Day.Monday)

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Go to Power Query, Add Column tab, Conditional Column. Then 

you will get this.

then go to the Transform tab, select your new column, select Fill, select down.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Thank you! I had it up until the Conditional Column part but wasn't aware of the Fill Down function. This solves my problem!

[Nathaniel_C]

Hi @Anonymous ,

You may want to look at the book M is for (Data) Monkey by @KenPuls . It has lots of great knowledge on Power Query.

Nathaniel