Forum Discussion
PowerQuery Filter Out Data from Last Two Dates
- 5 years ago
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
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