Select maximum values in a column according the file (defined in another column)

[Anonymous]

Hi all, I need your help.

I explain myself with an example:

This is what I have:

FILE     VALUE

File 1     0

File 1     5

File 1     1

File 1     4

File 2     10

File 2     4

File 2     9

and this is result I am looking for:

FILE     VALUE

File 1     5

File 2     10

Can you help me?

Thank you so much!

[Anonymous]

Thank you so much!!

It works!!!

You are incredible!! 🙂

[AlB · Accepted solution]

Hi @Anonymous 

Just use Group By. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSVUwVNJRMlCK1UHimqJyDVG5JgiuEUjWAJWPJm2pFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FILE", type text}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FILE"}, {{"Count", each List.Max([VALUE]), type table [#"FILE  "=nullable text, VALUE=nullable number]}})
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