1

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.

1 Answer 1

0

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.

1
  • There is nothing hard about that, that is what I have done. A solution to the original question would still be helpful to know for the sake of completeness and future reference. Why does each Date.EndOfWeek(...) work in the AddColumn and not in the TransformColumns? What's the appropriate modification to the TransformColumns? Can/should one apply these reasons/methods/strategies toward the next similar situation? Etc. If adding a new column and deleting the old is the most appropriate way, or the only way to do it then it would be helpful to know that as well.
    – fred
    Jun 28, 2016 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.