Forum Discussion

MrPingu's avatar
MrPingu
Frequent Visitor
7 years ago
Solved

Return only rows with max value from specific column

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 S...
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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"