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])

[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