Forum Discussion

stribor45's avatar
stribor45
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Selecting 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...
Need Help
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    1 year ago
    Table.FromRecords(
    Table.Group(Source, "id", {"recs", (x) => Table.Max(x, {"Date", (w) => - w[Decision column]})})[recs]
)
stribor45's avatar
stribor45
Icon for Post Prodigy rankPost Prodigy
1 year ago

For 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's avatar
ronrsnfld
Icon for Super User rankSuper User
1 year ago
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: