How to remove duplicate values from a column in a different table

Hi Anonymous 

How about using Power Query to create a new table to replace the original 'Purchase Orders' table?

First duplicate 'Deals' query in Power Query Editor, combine Vendor 1 internal PO No. & Vendor 2 internal PO No. columns into a single column and remove duplicated & null values. 

    #"Vendor 1 PO No " = #"Changed Type"[#"Vendor 1 PO No."],
    #"Vendor 2 PO No " = #"Changed Type"[#"Vendor 2 PO No."],
    #"All PO No" = List.Distinct(List.Combine({#"Vendor 1 PO No ",#"Vendor 2 PO No "})),
    #"Converted to Table" = Table.FromList(#"All PO No", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Column1] <> null and [Column1] <> ""),

Then use Merge Queries feature to combine data from Purchase Orders table into this new table. Select Purchase Order Number column as the matching column. For Join Kind, if you want to keep all PO Numbers from both tables, select Full Outer. If you only want to keep PO Numbers from the new table, select Left Outer. Then you will get a new column which contains data from Purchase Orders table.

At last, expand the Purchase Orders column and select columns you want to keep in the new table.

You will get a new table in which the PO Number column has all distinct values from two columns in Deals table and other columns from 'Purchase Orders' table. They are matched on the PO Number values. You can use this new table to replace the original 'Purchase Orders' table in your model. You can remove old relationships between tables and create new relationships with this new table.

Attached a pbix for your reference.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.