Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
  • mahoneypat's avatar
    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