topic Power Query EndOfMonth in Power Query

Power Query EndOfMonth

Akbarov — Thu, 28 Jul 2022 20:17:08 GMT

Hello community,

I have data where dates stored as number.

202006 <--as 2020/06

So I created new column in PQ to transfor it to real date.

= Table.AddColumn(#"Added Custom", "MonthFrom", each Date.FromText(Number.ToText([Month From]) & "01"))

I added "01" to the end of numbers. But I want to know , how can I add EndOfMonth instead of "01"?

Thank you.

Re: Power Query EndOfMonth

HotChilli — Thu, 28 Jul 2022 21:06:50 GMT

There is a Date.EndOfMonth function (it's available from the interface too -> Add Column->Date->Month) and it needs a legitimate date to work on so this would be additional to the step that adds "01" to make a valid date.

Re: Power Query EndOfMonth

AlexisOlson — Thu, 28 Jul 2022 21:12:00 GMT

You could use the Date.EndOfMonth() function with what you already have.

= Table.AddColumn(#"Added Custom", "MonthFrom", Date.EndOfMonth(Date.FromText(Number.ToText([Month From]) & "01")))