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

Question

Hi all, I need your help.&nbsp;I explain myself with an example:This is what I have:FILE&nbsp;&nbsp;&nbsp;&nbsp; VALUEFile 1&nbsp;&nbsp;&nbsp;&nbsp; 0File 1&nbsp;&nbsp;&nbsp;&nbsp; 5File 1&nbsp;&nbsp;&nbsp;&nbsp; 1File 1&nbsp;&nbsp;&nbsp;&nbsp; 4File 2 &nbsp;&nbsp;&nbsp; 10File 2 &nbsp;&nbsp;&nbsp; 4File 2 &nbsp;&nbsp;&nbsp; 9&nbsp;and this is result I am looking for:FILE&nbsp;&nbsp;&nbsp;&nbsp; VALUEFile 1&nbsp;&nbsp;&nbsp;&nbsp; 5File 2 &nbsp;&nbsp;&nbsp; 10&nbsp;Can you help me?&nbsp;Thank you so much!&nbsp;&nbsp;

alb · Accepted Answer

Hi Anonymous&nbsp;
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"
&nbsp;
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&nbsp;
&nbsp;

Forum Discussion

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

2 Replies

Related content

Running Maximum

un define

Rows - Maximum Date

Fabric User Define Functions

Maximum Name according to date