Forum Discussion
Filter only rows with the earliest date
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 ...
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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.