PowerQuery Filter Out Data from Last Two Dates

[ET1995]

I have a dataset which looks like this:

File	Date	Item	PDQ	E#	E Type	Available
1	2/2/2021	A	5	108	Shelf	Y
1	3/4/2021	C	3	23	Shelf	Y
2	4/3/2021	D	2	4	Shelf	Y
2	4/3/2021	E	5	67	Bulk	Y
3	5/2/2021	G	7	4	Semi	N
3	4/2/2021	B	6	11	Bulk	Y

The date of any file is the minimum date minus 7 days. I only want to add a table in power query which only queries data from the latest 2 files, which in this case will be files 2 and 3. This is just a sample I have a lot of files that keep adding s I need this to be dynamic. Please guide me how to do this in power query directly (instead of through filters)

[mahoneypat · Accepted solution]

You can sort by your date column and then just keep the top two rows.  See example below.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLSB0IDIxDTEYhNgdjQwAJIBmek5qQB6UilWB2IUmN9E5hSZxAXpNsYQ6URkGWibwxT6QJSBRIipNAVaruZOZBwKs3JhisE2WGKcKY7EJvDTEzNzQRSfnB1Jgh1TiDDQN4xRDEwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [File = _t, Date = _t, Item = _t, PDQ = _t, #"E#" = _t, #"E Type" = _t, Available = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Date", Order.Descending}})),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",2)
in
#"Kept First Rows"

Note that I wrapped the Table.Sort with Table.Buffer to make sure the sort order is maintained.

Pat