cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iron_dinges
Helper II
Helper II

A few records seem to be erroneously duplicating

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:

report bin duplicate.png

Checking in Power Query, there is only one record:

power query.png

 

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?

1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

After digging a bit deeper I discovered the issue:

show.png

As always, it's dirty data!

With collation allowing this to sneak through.

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!