Create a table with unique item from another table with duplicate

Question

I have a table called Item as shown below:

Note this table has duplicated ID and Item but Status is not.

I want to create a table from the Item table to have a unique ID, Item with Status, However, Item with the Sold Status is important than Not Sold and not sold is important than -- . for example, Coffee has three status but I want the table to pick only Coffee with Sold status. Another example, Monitor has 2 status "--" and "Not Sold", hence "Not Sold" should be returned.

The screenshot below shows the return table:

How can I achieve this?

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;- I would approach this by adding a new column with a sort key.&nbsp; Sold = 1, Not Sold = 2, -- = 3.&nbsp; The table can now be sorted by ID and Sort Key, and then use the Duplicate function.&nbsp; This would retain the first version of the ID with the Sold row.&nbsp; The key to making this work is the Buffer step.&nbsp; This make sure that the sort in applied before the distinct.&nbsp;let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLOT0tLTQUydHWVYnXQxILzc1IwRf3ySxTgMkZAAd/MnGxk1UhiKGqNgQLhiSWpRQjrTIDMgNQ8dJWmQAGfxIKS/AKEUhQx3KrhomYgV+TnZZbkI9mHKogwJRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Item = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Sort Key", each if [Status] = "Sold" then 1 else 
if [Status] = "Not Sold" then 2 else 3, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}, {"Sort Key", Order.Ascending}}),
    #"Buffer Table" = Table.Buffer( #"Sorted Rows" ),
    #"Removed Duplicates" = Table.Distinct(#"Buffer Table", {"ID"})
in
    #"Removed Duplicates"&nbsp;&nbsp;

Forum Discussion

Create a table with unique item from another table with duplicate

1 Reply

Related content

Working with copy job and creating duplicates

Duplicate ranking

Create table to remove duplicates

Creating Relationships with duplicative values

Table Merge creates duplicate records