Forum Discussion
Date.EndOfMonth
Today, I encountered a strange behavior with the Date.EndOfMonth function in Power Query.
Consider the source table below (in Excel):
| Date | Product | Qty |
| 19/07/2024 | A | 37 |
| 20/07/2024 | B | 12 |
I used the following code to add a new column to extract the end day of the month for each date:
= Table.AddColumn(Source, "Custom", each Date.EndOfMonth([Date]))
Surprisingly, the result was the first day of the next month instead of the expected end of the month, as shown below:
even if the type of this column change to DateTime it provide the same results, but it does work just when the data are in type Date.
I would guess that since neither of your date, columns are actually type date or even type datetime, that power query is treating it as a number and is rounding it up to the next number, which is the next day. Notice that your value has the time of 23:59:59.99999, which is as close to the end of the month as possible, but if you had a proper date value, it would have been 00:00:00, which would be the right date. Yours is a type misstep, not a Power Query issue.
--Nate
1 Reply
I would guess that since neither of your date, columns are actually type date or even type datetime, that power query is treating it as a number and is rounding it up to the next number, which is the next day. Notice that your value has the time of 23:59:59.99999, which is as close to the end of the month as possible, but if you had a proper date value, it would have been 00:00:00, which would be the right date. Yours is a type misstep, not a Power Query issue.
--Nate