Can I select MAX value in Table.SelectRows

[aTChris]

Hi everyone

I have a table of quotes from D365 CRM. During negotiation the quotes are revised to reflect the value of the deal. I need to create a table with unique quoteID's and the most recent quote which I can use the RevisionID for.

My thought is to use the Table.SelectRows function but I dont seem to be able to defined MAX as a condition.

e.g. 

QuoteID	Quote Name	RevisionID	Amount
1	Quote 1	0	£25000
1	Quote 1	1	£30000
1	Quote 1	2	£27500
2	Quote 2	0	£1500
2	Quote 2	1	£2750

Results

QuoteID	Quote Name	RevisionID	Amount
1	Quote 1	2	£27500
2	Quote 2	1	£2750

Any ideas?

Thanks

[aTChris]

@AlB 

Thanks for that. I think that will work. I have many other attributes in the table that I need to retain. I should be able to apply them in the grouping.

[AlB · Accepted solution]

Hi @aTChris 

Place the following M code in a blank query to see the steps.

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoszS9JVQCxDID40GIjUwMDA6VYHXRZw0MLwPLGBnB5sAhChRFEv7kpVN4ILmsEN90Qu6QhXKtSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuoteID = _t, #"Quote Name" = _t, RevisionID = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QuoteID", Int64.Type}, {"Quote Name", type text}, {"RevisionID", Int64.Type}, {"Amount", Currency.Type}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"QuoteID", Order.Ascending}, {"RevisionID", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"QuoteID", "Quote Name"}, {{"Count", each List.Last([Amount]), Currency.Type}})
in
    #"Grouped Rows"

or alternatively:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoszS9JVQCxDID40GIjUwMDA6VYHXRZw0MLwPLGBnB5sAhChRFEv7kpVN4ILmsEN90Qu6QhXKtSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuoteID = _t, #"Quote Name" = _t, RevisionID = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QuoteID", Int64.Type}, {"Quote Name", type text}, {"RevisionID", Int64.Type}, {"Amount", Currency.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"QuoteID", "Quote Name"}, {{"Count", each Table.Max(_, each [RevisionID])[Amount], Currency.Type}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers