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

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,088
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,299
Get Year from Date
then
Code:
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
endofyear.png
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,299
or
Get Year from Date then

Code:
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
endofyear2.png
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,088
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,299
you didn't say you want other columns
is this stock market classified?

but the first code should work with all columns
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,088
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,299
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-known Member
Joined
Mar 31, 2015
Messages
2,088
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,299
my free interpretation of these tables :devilish: 😇
DateDividendsYearRecord DatesPayment DatesC$ Amount
24/01/20201.05202027/01/202024/02/20201.05
23/01/20190.98201924/01/201922/02/20190.98
24/01/20180.91201825/01/201823/02/20180.91
24/01/20170.83201726/01/201724/02/20170.83
22/01/20160.79201626/01/201624/02/20160.79
22/01/20150.75201526/01/201524/02/20150.75
23/01/20140.67201427/01/201424/02/20140.67
22/01/20130.6201324/01/201322/02/20130.6
24/01/20120.54201226/01/201224/02/20120.54
24/01/20110.5201126/01/201124/02/20110.5
22/01/20100.5201026/01/201024/02/20100.5
22/01/20090.5200926/01/200924/02/20090.5
22/01/20080.5200824/01/200822/02/20080.5
23/01/20070.4200725/01/200723/02/20070.4
24/01/20060.64200626/01/200624/02/20060.32
24/01/20050.55200526/01/200524/02/20050.275
22/01/20040.46200426/01/200424/02/20040.23
23/01/20030.4200327/01/200324/02/20030.2
22/01/20020.36200224/01/200222/02/20020.18
 
Last edited:

Forum statistics

Threads
1,085,785
Messages
5,385,863
Members
401,975
Latest member
OnPoint

Some videos you may like

This Week's Hot Topics

Top