Filter a column in Power Query so that it contains only the last date of each year

[webportal]

Can anyone please advise on how to filter this column in Power Query so that it contains only the last date of each year?

So, this should contain only 3 rows:

• 31/12/2019
• 31/12/2020
• 31/03/2021

Thank you!

[v-angzheng-msft · Accepted solution]

 Hi, @webportal 

Create a secondary table

1. Copy a table named Table 2
2. Insert the year column in table 2
3. Group by year column to get the last date
4. merge table 1 and 2 to expand the last date column
5. Filter to filter null values

It must be noted that the data format of the date column cannot be text.

In order to test the correct logic, I changed the maximum date from December 31 to December 30

Sample:

Result:

Please refer to the attachment below for details

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samdthompson]

@v-angzheng-msft I think @webportal  is looking for the last date in the year, not the last date used in the year

[samdthompson]

Hello, you could try the last day of year funtion in power query, on a duplicate of your table then remove duplicates on the column, that would leave you with just the last days of the year: