Power Query returns 0 duplicates, but once loaded, dataset has duplicates?

[olimilo]

I have a composite dataset that I trimmed down to unique values in PQ, nulls and blanks removed:

Checking with Keep Duplicates, this dataset returns 0 duplicates. However, once I load the dataset, it returns 400+ duplicates:

I am going to use this as a table that will bridge the source of the composite dataset but as it stand, I am unable to because it returns a M:M relationship towards both tables. Is there a way to guarantee that the composite dataset will return 0 duplicates once loaded?

[v-hjannapu · Accepted solution]

Hi @olimilo,
Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @Akash_Varuna , for your reply regarding the Query.

To over come from your issue Apply Trim, Clean, and Uppercase/Lowercase steps in Power Query before removing duplicates. This ensures your bridge table truly has unique values, and helps avoid the many-to-many issue in your data model.

I tested with my sample data, and its working fine please find the attached screenshot and pbix file for your reference.

In my sample data, the company names appear the same, but they are linked to different regions and have different customer IDs  that’s why they show up multiple times.

If this answer was helpful and pointed you in the right direction, please consider accepting it as solution and kindly give a kudos it will benefit other community members in the community. If you have any further questions, feel free to let us know.

Regards,
Harshitha.
Community Support Team.

[v-hjannapu]

Hi @olimilo,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank You.

[v-hjannapu]

Hi @olimilo,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[Akash_Varuna]

Hi @olimilo Power Query may process data differently than Power BI, causing duplicates to reappear after loading. Ensure columns like COMPANY_NAME are cleaned with Text.Trim, Text.Clean, and standardized for case using Text.Lower or Text.Upper. Check and set proper data types, then reapply Remove Duplicates as the final step in Power Query.

[danextian]

This is the case with Power Query. ABC is different from ABc so they're not considered duplicates but they're loaded into the model as either ABC or ABc, whichever the engine sees first which the model now sees as duplicates.

But instead of standardizing the same column, I would duplicate it and standardize it instead, apply remove duplicates to it then delete after as the users might not want to see everything in upper or lower case.