Solved: Date.EndOfMonth

Omid_Motamedise · ‎12-02-2024

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.

If my answer helped solve your issue, please consider marking it as the accepted solution.

Anonymous · ‎12-02-2024

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

View solution in original post

Anonymous · ‎12-02-2024

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