Filter only rows with the earliest date

[Anonymous]

Hello everyone,

I'm trying to filter a table on PowerQuery based on the date.

I have a list of several items, and these items might have been purchased on several dates, and I want to filter only the earliest line of each item (last purchase).

I'm all good doing that as calculated columns and stuff, but I was hoping to do it on PowerQuery to lighten the dataset. I'm struggling to find a solution to this.

Any help will be appreciated!

Thank you!

[Anonymous]

Just FYI, each line of the same item has a different Purchase Order number, so I can't use Group By because I need both the latest late and the PO number of that latest date.

[dax · Accepted solution]

Hi @Anonymous , 

You could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDIwN9I31DpVgdiKARTNBQ3wguaIwQNDQFiyah6kcIGiEEjeGCSPqBKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, po = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"po", Int64.Type}, {"date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"name", Order.Ascending}, {"date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"name"}, {{"all", each _, type table [name=text, po=number, date=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"po", "date", "index"}, {"po", "date", "index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([index] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"index"})
in
    #"Removed Columns1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisMendoza]

@Anonymous -

Please post/share sample data.