Filter a list of dates to final day of year in PQ.

[DRSteele]

Can someone please help me?

I have a list of Dates (an incomplete list) as the first column in Power Query. I wish to filter out everything to the last day of each year.

It's stock market data, so sometimes the final day is 12/29, sometimes it's 12/30 and sometimes it's 12/31.

 

[sandy666]

Get Year from Date
then

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Reverse = Table.ReverseRows(Type),
    RemDuplicate = Table.Distinct(Reverse, {"Year"}),
    ReverseBack = Table.ReverseRows(RemDuplicate)
in
    ReverseBack

 

[sandy666]

or
Get Year from Date then

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Group = Table.Group(Type, {"Year"}, {{"Date", each List.Max([Date]), type date}})
in
    Group

 

[DRSteele]

Sandy, thank you for your prompt reply.

That does work, but it deletes all the other columns. I can't figure out how to retain them. I am trying to filter my list down to year-end dates as well dividend dates throughout the year, a condition I think I figured out.

I am not very good at this M code. Can you please help me?

 

[sandy666]

you didn't say you want other columns
is this stock market classified?

but the first code should work with all columns

 

[DRSteele]

I wanted to keep it simple, but I see you need that info - sorry.

No, the stock market data is freely available. I don't understand.

The first code works after I figured out how to include the prior operation names. M-code is hard.

 

[sandy666]

No, the stock market data is freely available. I don't understand.

maybe would be easier to post a link

but try these steps first

1. select Date column
2. tab Add Column - Date - Year
3. tab Transform - Reverse Rows
4. select Year column
5. tab Home - Remove Rows - Remove Duplicates
6. tab Transform - Reverse Rows

 

[DRSteele]

Well, there's no link per se. I downloaded from yahoo.finance the history of Royal Bank (RY) back to 2001 so I could calculate my total return. I had another table that I cut-and-pasted from RY and update regularly. I wanted to merge the two tables and filter it down to year-end dates and dividend dates. I used RY as an example so that I can solidify the procedure for other stocks.

 

[sandy666]

here is an example with live data from these services Royal Bank

 

[sandy666]

my free interpretation of these tables ?

Date	Dividends	Year	Record Dates	Payment Dates	C$ Amount
24/01/2020	1.05	2020	27/01/2020	24/02/2020	1.05
23/01/2019	0.98	2019	24/01/2019	22/02/2019	0.98
24/01/2018	0.91	2018	25/01/2018	23/02/2018	0.91
24/01/2017	0.83	2017	26/01/2017	24/02/2017	0.83
22/01/2016	0.79	2016	26/01/2016	24/02/2016	0.79
22/01/2015	0.75	2015	26/01/2015	24/02/2015	0.75
23/01/2014	0.67	2014	27/01/2014	24/02/2014	0.67
22/01/2013	0.6	2013	24/01/2013	22/02/2013	0.6
24/01/2012	0.54	2012	26/01/2012	24/02/2012	0.54
24/01/2011	0.5	2011	26/01/2011	24/02/2011	0.5
22/01/2010	0.5	2010	26/01/2010	24/02/2010	0.5
22/01/2009	0.5	2009	26/01/2009	24/02/2009	0.5
22/01/2008	0.5	2008	24/01/2008	22/02/2008	0.5
23/01/2007	0.4	2007	25/01/2007	23/02/2007	0.4
24/01/2006	0.64	2006	26/01/2006	24/02/2006	0.32
24/01/2005	0.55	2005	26/01/2005	24/02/2005	0.275
22/01/2004	0.46	2004	26/01/2004	24/02/2004	0.23
23/01/2003	0.4	2003	27/01/2003	24/02/2003	0.2
22/01/2002	0.36	2002	24/01/2002	22/02/2002	0.18