Forum Discussion
remove duplicates based on values in 2 columns
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 |
Hi Anonymous
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-byIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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"
3 Replies
Hi Anonymous
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-byIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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"