A few records seem to be erroneously duplicating

[iron_dinges]

I'm having an issue where a handful of rows in my table appear to be duplicating within the Power BI report.

What I mean by this is I'm seeing counts of more than 1 for the primary key of a few records - even though there is only 1 value per PK in the source as well as the Power Query editor.

My table consists of the following:

3 columns: Warehouse, Bin, BinType

The primary key is a composite key of Warehouse and Bin.

When I query the number of records per primary key in SQL, there are no duplicates.

In Power Query, I add the primary key as a custom column using [Warehouse]&[Bin].

This is what I'm seeing in the report:

Checking in Power Query, there is only one record:

This table is not being joined to any other tables.

Am I missing something?

This is the same method I always use to check and prepare my data when building a report to ensure I can build the correct relationships in the model. I just can't understand where this is coming from. It is preventing me from making a One-to-Many join, and I can't remove a record that doesn't exist from my data source.

I have 4 records out of 7600 that show this issue.

Where is this duplicate coming from?

[amitchandak]

@iron_dinges , can you share the data model diagram. Because there is some problem with join .

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[iron_dinges]

@amitchandak As stated in the opening post, I have not joined this table to any other tables.

This is a simple table being imported from SQL.

I will try to prepare a sample pbix for you to review.

[amitchandak]

@iron_dinges , power query shows only 1000 records at first go. Can sort and check of there are more records

[iron_dinges]

@amitchandak 

After digging a bit deeper I discovered the issue:

As always, it's dirty data!

With collation allowing this to sneak through.

[mahoneypat · Accepted solution]

Yes.  Since M is case sensitive but the data model/DAX side is not, this happens (they appear different in M but same in DAX).  You can just add an UPPERCASE step in your query prior to removing duplicates to address it.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat