Transform Date to Start of the Week giving the End of the Week date instead

[zjwright]

Hello All,

I checked the community but did not see a post for this bug yet. I have noticed when I do a simple transformation on a date column, sometimes (not everytime) when I transform the date to be the start of the week, power query actually converts to the end of the week instead. Seems like a fairly simple bug, has anyone else noticed this? If so does anyone know if Microsoft is aware of the issue yet?

[zjwright]

Hi All, 

Thanks for your responses. I am seeing this issue in the power query editor when using the transform tab & selecting "start of week" when transforming a date column

[ronrsnfld · Accepted solution]

I doubt it is a bug at all.

The formula used by the transform tab assumes a default first day of the week. The transform tab does not allow you to change that. It sounds like your system is assuming a first day of the week that is different from what you expect.

You will need to edit the actual code.

In the formula bar, you probably see something  like:

= Table.TransformColumns(#"Changed Type",{{"date",Date.StartOfWeek, type date}})

although the #"Changed Type" variable will be the name of whatever the previous step to your transformation is named (in the Applied Steps window), and "date" will be the name of the date column you are transforming.

To specify a particular day of the week to be the first day, you can edit it to something like (if Monday is your first day):

= Table.TransformColumns(#"Changed Type",{{"date",each Date.StartOfWeek(_,Day.Monday), type date}})

[v-stephen-msft]

Hi @zjwright ,

I guess you're talking about the Date.StartOfWeek function, right?

If so, there's an optional parameter for you to set the start day number.

Please try setting a specific start day for your formula.
For example, find the start of the week for Tuesday, October 11th, 2011, using Monday as the start of the week.

Date.StartOfWeek(#datetime(2011, 10, 11, 8, 10, 32), Day.Monday)

Reference: Date.StartOfWeek - PowerQuery M | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ronrsnfld]

I cannot reproduce that.

Perhaps there is something incorrect about your coding.

Please provide examples of your data, along with your actual code (from the Advanced Editor) and your actual and expected results so we can help you troubleshoot this.

[Omid_Motamedise]

Can you give an example of a date and its transformed value, and what is your prefered starting day for a week