Remove duplicate rows based on max value of a different column

[Zanith]

Hi All,

I'm attempting to filter some of my data and I'm having problems getting it correct. I'm pulling my data in from SQL and cleaning in up in power query before visualizing it in Power BI. This is data of sales orders captured in our system which, over time, might have modifications made to them resulting in our system archiving off the older versions and applying a version number to each row of data. Each sales order has it's own unique number (Sales Order Number) which can have multiple rows of data (a row for each item on the sales order) and, as a result of the archiving process, each row will have a Version number.

I need to be able to extract all rows of a Sales Order Number which match the max value of the Version.

A rough example of the data I'm pulling is below.

SALES ORDER NUMBER	VERSION	ITEM
10000	1	Item 1
10000	1	Item 2
10000	1	Item 3
10000	2	Item 1
10000	2	Item 2
10000	2	Item 3
10000	3	Item 1
10000	3	Item 2
10000	3	Item 3
10001	1	Item 1
10001	2	Item 1
10002	1	Item 3
10003	1	Item 2
10003	1	Item 4
10003	2	Item 2
10003	2	Item 4

<tbody>
</tbody>

And here is an example of how I need this data to be transformed.

SALES ORDER NUMBER	VERSION	ITEM
10000	3	Item 1
10000	3	Item 2
10000	3	Item 3
10001	2	Item 1
10002	1	Item 3
10003	2	Item 2
10003	2	Item 4

<tbody>
</tbody>

Any help is appreciated!

 

[MarcelBeug]

You can first group on Order and version, with aggregation operation "All Rows".
Next you can do the same, but only on Order, and adjust the generated code to select only the maximum version number (for each order).
Now you can expand the appropriate columns (twice).

Generated/adjusted code (only step #"Grouped Rows1" adjusted):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SALES ORDER NUMBER", Int64.Type}, {"VERSION", Int64.Type}, {"ITEM", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SALES ORDER NUMBER", "VERSION"}, {{"AllData", each _, type table}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"SALES ORDER NUMBER"}, {{"AllData", each Table.Max(_, each [VERSION]), type record}}),
    #"Expanded AllData" = Table.ExpandRecordColumn(#"Grouped Rows1", "AllData", {"VERSION", "AllData"}, {"VERSION", "AllData.1"}),
    #"Expanded AllData.1" = Table.ExpandTableColumn(#"Expanded AllData", "AllData.1", {"ITEM"}, {"ITEM"})
in
    #"Expanded AllData.1"