remove duplicates based on values in 2 columns

[MXMXM]

Hello there, 

I've got 2 tables:

table 1 contains sales data based on a key which is product-volume combination. sales are captured on this key

table 2 is my item master table, I can have several item codes associated with this product-volume combination. most of the product properties are based on the item codes.

in table 2, I want to only keep the row with the highest/ latest item code so I won't have duplices in product-size. 

Question is how to do this.

hope I make myself clear and looking forward to feedback. 

regards, M

table 1

product-size	volume
abc-0.7	500
def-0.5	900

table 2

product-size	item code	price
abc-0.7	1001	1.25
abc-0.7	1020	1.25
abc-0.7	1264	1.25

[Ritaf1983 · Accepted solution]

Hi @MXMXM 
I am not sure that I understood your goal completely,
It sounds like you need a group by your data, with aggregation by maximum code, if it is on separate columns you can concatenate them before the grouping.
Tutorial to concatenating columns:
https://excelchamps.com/power-query/concatenate/

Group by here:
https://learn.microsoft.com/en-us/power-query/group-by

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[MXMXM]

looks like you did understand what I was looking for. all good now. thanks

[Ahmedx · Accepted solution]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxK1jXQM1fSUTI0MDAEUXpGpkqxOqgyRgY4ZIzMTOAysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"product-size" = _t, #"item code" = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product-size", type text}, {"item code", Int64.Type}, {"price", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"product-size"}, {{"Count", (x)=>Table.SelectRows(x, each [item code]=List.Max(x[item code]))}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"product-size", "item code", "price"}, {"product-size", "item code", "price"})
in
    #"Expanded Count"

[Ritaf1983 · Accepted solution]

Hi @MXMXM 
I am not sure that I understood your goal completely,
It sounds like you need a group by your data, with aggregation by maximum code, if it is on separate columns you can concatenate them before the grouping.
Tutorial to concatenating columns:
https://excelchamps.com/power-query/concatenate/

Group by here:
https://learn.microsoft.com/en-us/power-query/group-by

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly