Forum Discussion

009co's avatar
009co
Helper IV
3 years ago
Solved

Get rows that have max value

My query uses Group By to group by three categorical columns and get sum of a Price column. The resulting table looks like below.   I want to keep only the rows that have the max value in the Price...
Need Help
  • Poohkrd's avatar
    Poohkrd
    3 years ago

    Hi, try this one:

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
    Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
    Grouped = Table.Group(Typed, {"Purchase Order"}, {{"recs", each Table.Max(_, " Price "), type record}}),
    Recs = Table.FromRecords( Grouped[recs] )
in
    Recs

     

    You can also use List.Max, but with a small addition to the code. This edit is needed to bypass the lazy calculation of lists in the M language:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
    Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
    Grouped = Table.Group(Typed, {"Purchase Order"}, {{"tabs", each Table.SelectRows(_, let latest = List.Max([#" Price "]) in each [#" Price "] = latest), type table}}),
    Recs = Table.Combine( Grouped[tabs] )
in
    Recs

     

     

Poohkrd's avatar
Poohkrd
Advocate I
3 years ago

Hi, try this one:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
    Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
    Grouped = Table.Group(Typed, {"Purchase Order"}, {{"recs", each Table.Max(_, " Price "), type record}}),
    Recs = Table.FromRecords( Grouped[recs] )
in
    Recs

 

You can also use List.Max, but with a small addition to the code. This edit is needed to bypass the lazy calculation of lists in the M language:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
    Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
    Grouped = Table.Group(Typed, {"Purchase Order"}, {{"tabs", each Table.SelectRows(_, let latest = List.Max([#" Price "]) in each [#" Price "] = latest), type table}}),
    Recs = Table.Combine( Grouped[tabs] )
in
    Recs