Append Queries and Remove Duplicate

[VoltesDev]

Hi,

I have two queries added in Power Query, and at each of the table, added custom column with value of concate few fields, like this:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "distributor_date_cust_product", each Text.Combine({Text.From([DISTRIBUTORID]), Text.From([DATEID]), Text.From([CUSTID]), Text.From([PRODUCTID])}, "&")),

- Next step is create Reference to these two table, and at each I'm leaving only that added custom columnm by using "Remove Other Columns" feature,  so each table only contain 1 column (same column name). then "Remove Duplicates".

- Then, on the 2nd table, use "Append query" to add the other table to this current table, and then execute another "Remove Duplicates"

Issue is when adding this table to Data Model, I cannot use it to make reference since Power Pivot will still telling me there is duplicate value for this table. 

May I know what is wrong ? By right, ain't the feature "Remove Duplicates" solve this issue already ?

Thanks

[v-mdharahman · Accepted solution]

Hi @VoltesDev,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are unable to create reference between tables but because of duplicate values you are unable to do so. I tried to recreate your scenario with a sample data where I followed your steps of :

* Creating a custom column and removing all other columns:

* Appending the first table on second table:

* After appending removing all the duplicates:

After following all the steps, ensure there is no hidden duplicate value by checking for Null Values.

* Click on the filter icon in custom column created. If you see NULL, remove it by unchecking NULL.

* Check for hidden duplicates by using "Group By". On Transform pane select group by and then select the custom

   column. In New column put "count" and set it to "count rows" and "OK".

After this you will see a new column with count as 1. If count > 1, then duplicates still exists.

* In model view I was able create reference with my appended table (table2 Ref)

Try to cross check for any duplicates before loading the data from Power Query to PowerBi Desktop.

If I misunderstand your needs or you still have problems on it, can you please elaborate more and provide some sample data so that our community members can reproduce the scenario and come up with the best possible solution.  

I would also take a moment to thank @Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Akash_Varuna]

Thank You @v-mdharahman  , For your kind words and also for the more detailed solution

[v-mdharahman]

Hi @VoltesDev,

If you are able to solve your issue then, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Akash_Varuna]

Hi @VoltesDev , I think the issue arises because of "Remove Duplicates" is applied before the append please do try these steps 

• After appending the two tables, apply Remove Duplicates again on the appended result, not just on individual tables before appending.
• Double-check the concatenated values for any hidden differences hat might prevent rows from being recognized as duplicates.
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance