Forum Discussion
stribor45
Post Prodigy
Post ProdigySelecting Rows by Max Date and something else
I have table where multiple rows with same ID may exist but with different or sometimes same values in Date column. Of course I can group this by ID and select ones with MAX date however since some...
Table.FromRecords( Table.Group(Source, "id", {"recs", (x) => Table.Max(x, {"Date", (w) => - w[Decision column]})})[recs] )
ronrsnfld
Super User
Super UserHow are you choosing that row now?
In general, you would just add to the appropriate logic to the condition argument of the Table.SelectRows function.
stribor45Post Prodigy
Post ProdigyFor example the top is the table I start with and below it is the table I want to end up with. If the date column is the same for the the particular id then I keep the roiw with lowest number in Decision column
- ronrsnfld
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyBGIzfUNDfSMDIxDfUClWByHrhCZrBJY1BQG4XmNUrTBJZ7CkCUzSGCxpBgJwnUaoOs1BAMhzQ5U0U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Type = _t, Date = _t, #"Decision column" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Type", type text}, {"Date", type date}, {"Decision column", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, { {"MaxDate", (t)=> [a=Table.SelectRows(t, each [Date]=List.Max(t[Date])), b=Table.SelectRows(a, each [Decision column]=List.Min(a[Decision column]))][b], type table [id=nullable number, Type=nullable text, Date=nullable date, Decision column=nullable number]}}), #"Expanded MaxDate" = Table.ExpandTableColumn(#"Grouped Rows", "MaxDate", {"Type", "Date", "Decision column"}) in #"Expanded MaxDate"From your data:
- AlienSx
Table.FromRecords( Table.Group(Source, "id", {"recs", (x) => Table.Max(x, {"Date", (w) => - w[Decision column]})})[recs] )