Return only rows with max value from specific column

[MrPingu]

I am trying to only get the max values from a certain column in a table
Data looks like this:
SalesOrderArchive
SalesOrderNo ;  Version_No ;MoreColumns
SO1 ; 1 ; Datalines
SO1 ; 2 ; Datalines
SO2 ; 1 ; Datalines
SO3; 1 ; Datalines
SO3; 2 ; Datalines
SO3; 3 ; Datalines

I want for every disctinct SalesOrderNo. the max version so output should be
SalesOrderNo ;  Version_No ;MoreColumns
SO1 ; 2 ; Datalines
SO2 ; 1 ; Datalines
SO3; 3 ; Datalines




I tried to do the following but it's only returning the max value of VersionNo

SalesLinesArchive_table2 = List.Max(VerkooporderarchiefSalesLinesArchive_table[Version_No])

[androo235]

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?

[Anonymous]

Add index row and filter out index 1.

[marcelhenrique]

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

[v-frfei-msft]

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"

[HotChilli]

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

[MrPingu]

Maybe I didn't fromulat my question well enough.

I want for every  disctinct SalesOrderNo. the max version so output should be
SO1 ; 2 ; Datalines
SO2 ; 1 ; Datalines
SO3; 3 ; Datalines

[HotChilli · Accepted solution]

If you 'Group By' SalesOrderNo and include 'All Rows'

then Expand the table that results.

You will have to remove duplicates and tidy as necessary

[sandermatthijs]

In my opinion, @Anonymous's solution below is simpler: simply a) sort table, then b) add an Index, c) filter Index number. I tried Grouping function; I was NOT able to get this to work using standard Group By: the Max command does NOT choose the value ascociated (same row?) with Max of for instance date. The trick is in your last comments; use the All Rows, then 'clean up'. You then need to use a 'Max.Table' command. By which time you are in the weeds of PowerQuery, per this video: How to Group By Maximum Value using Table.Max in Power Query - YouTube