Excel Power Query EndOfWeek

Question

I have a Power Query column of dates. I need to transform the rows of this column to the end of their week. How do I do this with the end of the week being based on Sunday?

The default "Calculated End of Week" transformation bases the end of the week as Saturdays.

This is the formula generated by the Query editor that I need to adjust:

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

Here is some MSDN info on the Date.EndOfWeek function: MSDN Date.EndOfWeek

Here are a few things I've tried, each resulting in an error:

= Table.TransformColumns(#"Changed Type",{{"WorkDate", Date.EndOfWeek(, Day.Sunday), type date}})
= Table.TransformColumns(#"Changed Type",{{"WorkDate", Date.EndOfWeek("WorkDate", Day.Sunday), type date}})
= Table.TransformColumns(#"Changed Type",{{"WorkDate", Date.EndOfWeek(WorkDate, Day.Sunday), type date}})

Update:

I found a solution that is a sort of a work around. Based on the results of a google search I replaced the Table.TransformColumns function with the following Table.AddColumn function as follows:

= Table.AddColumn(#"Changed Type", "WeekEnding", each Date.EndOfWeek([WorkDate], Day.Monday), type date)

However, I would still like to know how to achieve this by modifying the existing column rather than creating a new column (then deleting the old one).

I also realized that I needed to change my second parameter in the EndOfWeek function to Monday instead of Sunday, the parameter asks for the first day of the week.

Answer 1

I'm not seeing what is so hard about adding a derived column then dropping the old source? That's a very common operation in PQL and the impact on performance, code etc will be trivial.

I typically leave that "clean up" step of Choosing Columns until the end of the Query where you can remove multiple columns in one step. Sometimes intermediate steps wipe out those "working out" columns anyway.