Power Query extract last day value in a table range

%3CLINGO-SUB%20id%3D%22lingo-sub-2046102%22%20slang%3D%22en-US%22%3EPower%20Query%20extract%20last%20day%20value%20in%20a%20table%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046102%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20wonder%20if%20there%20is%20an%20easy%20way%20with%20PQ%20to%20get%20a%20from%20a%20data%20table%20-%26gt%3B%20a%20summary%20table%20(for%20the%20last%204%20year%20that%20each%20year%20will%20be%20in%20a%20separate%20row%20with%20the%20following%20columns-%3CBR%20%2F%3E1.%20open%20rate%20of%20the%20first%20day%20of%20the%20year%20(%3CSTRONG%3Enot%20always%3C%2FSTRONG%3E%2001%2F01%2F20XX)%3CBR%20%2F%3E2.%20close%20rate%20of%20the%20last%20day%20of%20the%20year%20(%3CSTRONG%3Enot%20always%3C%2FSTRONG%3E%2031%2F12%2F20XX)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Ethe%20raw%20data%20is%20from%20yahoo%20with%20open%20and%20close%20rates%20for%20GOLD%20stock%20(attached%20please%20find)%26nbsp%3B%3C%2FP%3E%3CP%3Esometimes%20the%20first%20trade%20day%20in%20a%20month%20is%202nd%20or%203rd%20so%20I%20didn't%20find%20any%20pattern%20to%20use...%3CBR%20%2F%3EI%20succeed%20to%20get%20the%20job%20done%20with%20simple%20excel%20formulas%20bu%20PQ%20is%20a%20game%20changer%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3Emy%20idea%20was%20to%20marge%20the%20same%20table%20again%20with%20an%20index%20and%20keep%20a%20unique%20row%20(transform-%20group%20by%20Year)%20but%20I'm%20still%20trying%20this%3CBR%20%2F%3E%3CBR%20%2F%3Ethank%20you%20in%20advance%3C%2FP%3E%3CP%3ETomer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2046102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046171%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20extract%20last%20day%20value%20in%20a%20table%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046171%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%2C%20can%20you%20please%20attach%20it%20again%20I%20can't%20see%20the%20file%3CBR%20%2F%3Ethank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046146%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20extract%20last%20day%20value%20in%20a%20table%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923573%22%20target%3D%22_blank%22%3E%40TomerG6%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20two%20solution%20with%20dynamic%20array%20formulas%20and%20with%20PQ%20(used%20only%20UI-available%20commands).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046968%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20extract%20last%20day%20value%20in%20a%20table%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923573%22%20target%3D%22_blank%22%3E%40TomerG6%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVariant%20with%20formulas%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20454px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245140i8546B1BE251D5193%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20convert%20source%20data%20into%20the%20table%20Source%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EOpen%20(PY)%0A%3DIFNA(INDEX(Source%5BOpen%5D%2CMATCH(MINIFS(Source%5BDate%5D%2CSource%5BYear%5D%2C%5B%40Year%5D-1)%2CSource%5BDate%5D%2C0))%2C%22no%20data%22)%0A%0AOpen%20(CY)%0A%3DIFNA(INDEX(Source%5BOpen%5D%2CMATCH(MINIFS(Source%5BDate%5D%2CSource%5BYear%5D%2C%5B%40Year%5D)%2CSource%5BDate%5D%2C0))%2C%22no%20data%22)%0A%0AClose%20(CY)%0A%3DIFNA(INDEX(Source%5BClose%5D%2CMATCH(MAXIFS(Source%5BDate%5D%2CSource%5BYear%5D%2C%5B%40Year%5D)%2CSource%5BDate%5D%2C0))%2C%22no%20data%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies

@TomerG6 

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

 

@TomerG6 

Variant with formulas

image.png

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")