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.