FIlter specific dates from a table.

[glaungani]

hi, i have a dates table. (eg. below). I would like to use a query to filter only the last date for the Financial Year ending March 31, and the latest date available. (i.e the yellow shaded ones in table below).

would appreciate any help here.

thanks in advance.

 

[JEC]

Hi try this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    xList =  List.Generate( () => #date( 2010, 3 , 31 ), each _ <= #date( 2060, 3, 31), each Date.AddYears( _, 1 )) & {List.Max(cTypes[Dates])},      
    fltr = Table.SelectRows(cTypes, each List.Contains(xList,[Dates]))
in
    fltr

 

[glaungani]

Beautiful!! thank you very much @JEC. really appreciate the solution, worked like a charm. cheers.

 

[JEC]

You’re welcome! Cheers