Forum Discussion
PowerQuery Filter Out Data from Last Two Dates
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)
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
1 Reply
- mahoneypat
Microsoft Employee
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