Date.EndOfMonth

[Omid_Motamedise]

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.

[Anonymous · Accepted solution]

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