Power Query extract last day value in a table range

[TomerG6]

Hi All,

I wonder if there is an easy way with PQ to get a from a data table -> a summary table (for the last 4 year that each year will be in a separate row with the following columns-
1. open rate of the first day of the year (not always 01/01/20XX)
2. close rate of the last day of the year (not always 31/12/20XX)

the raw data is from yahoo with open and close rates for GOLD stock (attached please find) 

sometimes the first trade day in a month is 2nd or 3rd so I didn't find any pattern to use...
I succeed to get the job done with simple excel formulas bu PQ is a game changer :) 

my idea was to marge the same table again with an index and keep a unique row (transform- group by Year) but I'm still trying this

thank you in advance

Tomer

[#2]

@TomerG6 

Attached two solution with dynamic array formulas and with PQ (used only UI-available commands). 

[#3]

@TomerG6 

Variant with formulas

If convert source data into the table Source

Open (PY)
=IFNA(INDEX(Source[Open],MATCH(MINIFS(Source[Date],Source[Year],[@Year]-1),Source[Date],0)),"no data")

Open (CY)
=IFNA(INDEX(Source[Open],MATCH(MINIFS(Source[Date],Source[Year],[@Year]),Source[Date],0)),"no data")

Close (CY)
=IFNA(INDEX(Source[Close],MATCH(MAXIFS(Source[Date],Source[Year],[@Year]),Source[Date],0)),"no data")