Addressing Partial Duplicates

Question

Hi All, I have question about partial duplicates. I would like to find out about best practices and/or solution to get OrderTable &amp; CustomerInfo&nbsp; into DesiredTable.&nbsp;I have used various join to merge the table but I can't fix the CustomerID which are duplicate.I would like to have&nbsp; a distinct and unique ID for CustomerID. When I used ReplaceValue (111), it replaced all. Appreciate any advice. Thanks. Paul&nbsp;&nbsp;OrderTableCustomerID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CustomerName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Orders111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Paul&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Labtop111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Mary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Handphone112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Charlie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Switch&nbsp;&nbsp;CustomerInfoCustomerName&nbsp; &nbsp; &nbsp; &nbsp; AddressJohn&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Boston&nbsp;&nbsp;Paul&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; New YorkMary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TexasCharie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;California&nbsp;DesiredTableCustomerID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CustomerName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Orders&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Address100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Boston101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Paul&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Labtop&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;New York111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Mary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Handphone&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Texas112&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Charlie&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Switch&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; California&nbsp;&nbsp;

amitchandak · Answer

Anonymous , to fix this we need something unique. Like in sample data customer name is unique.&nbsp; You can add an index column 
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
&nbsp;
Starting with 100.&nbsp; in first table
and then you can merge two tables on customer name
https://radacad.com/append-vs-merge-in-power-bi-and-power-query

ba_pete · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
I'm not sure whether you have oversimplified your data here, but you are going to find it incredibly challenging to create the output table you require with the data structure you have provided.
&nbsp;
The first question that really needs addressing is: why are Customer ID's not unique?
The second question is: how unique are customer names? If they are actually John, Jack etc. you've got little chance. If they are ABC Widget Co., ACME Dynamite Inc. etc. (i.e. entirely unique and will not repeat) then you could probably join on the [CustomerName] fields in each table without using the [CustomerID] field at all.
&nbsp;
You *may* be able to get a unique [CustomerID] column by merging the [CustomerID] and [CustomerName] columns in Power Query. This would give you a new ID column that would show values like 111John, 111Paul, 111Mary, 112Charlie etc. although, as above, these types of names may repeat frequently like your [CustomerID] and therefore this may still not create a truly unique ID field. Even once you've done this, there's no way of doing the same in the CustomerInfo table to create a common field to join on.
&nbsp;
Pete

anonymous · Answer

Thanks for&nbsp; advice&nbsp; amitchandak&nbsp;,&nbsp;BA_Pete&nbsp;. I have simplified the table to convey&nbsp; a problem&nbsp; involving partial table, and duplication as error entry.&nbsp; The CustomerID is used as record identifer for an evolving Table and some CustomerID have errors , my thought is to simplify flow and correct CustomerID to be use as identifier.&nbsp; This CustomerID is just like an Social Security No. which is supposed to be distinct and unique.&nbsp;&nbsp;1) The new index column may work us single out unique entry but&nbsp; I probably have to figure out&nbsp; a way to ReplaceValue for&nbsp; CustomerID errors.&nbsp;&nbsp;&nbsp;2) Concatenate CustomerID &amp; Name probably wont work if I need to index to other table using CustomerID.&nbsp;&nbsp;

ba_pete · Answer

Anonymous&nbsp;,
&nbsp;
OK, so it looks like you really need to get to the root cause of why some of your [CustomerID] are showing as errors. Are the errors generated by Power Query, or are the errors in the data source and are being pulled into Power Query?
If you're not sure, select one of the error cells in Power Query and share a screenshot of what the errors look like. Please remove any sensitive information from your screenshot.
&nbsp;
Next, you need to look at whether you can bring [CustomerID] into your CustomerDetails table. If [CustomerID] exists in the system I would assume that it would feature in the&nbsp;CustomerDetails table. From there, a simple merge will get you what you need.
&nbsp;
Pete

Forum Discussion

Addressing Partial Duplicates

4 Replies

Related content

Duplicate ranking

kusto error- partial query failure

remove duplicate value within a row

Removing Duplicates

These email addresses re invalid or duplicate