Return only rows with max value from specific column

please see this article: https://www.ehansalytics.com/blog/2020/7/16/return-row-based-on-max-value-from-one-column-when-grouping

Add index row and filter out index 1.

If you want to return the row where Version_No is largest.

Table.Max(#"Promoted Headers", "Version_No")

where #"Promoted Headers" is the table.

You can then extract the SalesOrderNo

Hi MrPingu ,

Gourp by should work. 

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3VNJRAuGUxJLEnMy8VKVYHZiwEaawEXbVxriFsRgCEjZGEY4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sales = _t, #"Version NO" = _t, More = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type text}, {"Version NO", Int64.Type}, {"More", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Version NO"}, {{"max", each List.Max([Version NO]), type number}, {"more", each List.Max([More]), type text}})
in
    #"Grouped Rows"

I tried all three ways. My problem was to pick the latest file from many Excel files saved in a sharepoint folder using a naming convention of YYYYMMDD Name.xlsx. " Name.xlsx" is the same for every workbook so only the "YYYYMMDD" bit changes. Each of the files is about 115mb and has a 50 column 750k line table in it spat out by a SAPGUI report.

I liked the simplicity of the add an index row approx, but, at least for my largish data sets and source type it just took forever. Shame because this approach would have allowed me to keep some of the top level rows like the file name and some other "metadata" in the result. It took so long I cancelled and gave up. On a smaller dataset or better datasource (csv?), or in a local folder I think this would likely/maybe be fine.

In my case the grouping approach couldn't return a single row for me to then split out the "binary" column with the table needed. But, even if this had worked I think it would have amounted to the same thing as the "sort and then add an index and select #1" approach, and would then have taken ages to break out the table.

The table.max approach seems a more complex approach, but, it ran in an acceptable time. It produces a much longer looking query in the "query settings" window and indeed when viewed in "advanced editor" it is much longer, but, it runs in an acceptable time. So, overall it's the best approach. I wonder why?