Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Thigs
Helper III
Helper III

Many to Many - No Duplicates?

Hey all, 

I'm working with a new data set that comes from Excel. It has one column for SKUs as a unique identifier. I am trying to link it to a second dataset that has SKUs but not as a unique identifier (One:Many relationship). 

 

Power BI is insisting I can only do a Many:Many. I have removed all blank rows in Power Query, removed any rows where SKU is blank in the first data set in Power Query, and I have checked in both Excel and Power Query for duplicates - everything I've tried says there are no duplicate SKUs. Some might have duplicate values in other columns, but as best I can tell without going line by line, there are no duplicates in the unique identifier. Any ideas on why this would happen and what I can do about it?

 

Thanks!

9 REPLIES 9
jaylyn
Helper I
Helper I

I had the same issue. i removed nulls, then clean, then trim, and then made it all lowercase, and them removed duplicates and it worked!

DanIvanek
New Member

For me, it was caused by a blank value in the column that is used for connection. Even every record was unique including this blank value.

Try use remove empty and then reconnect

Morrit
New Member

I would try to first change the data types to text ( since they are an id of sorts i usually make all my id's text so there is no issues with accidentally combining the values as a value) 

After that do trim then make sure you remove duplicates after the trim step in power query so trim would be on top.

Anonymous
Not applicable

Hi @Thigs ,

Could you check the data type of SKU in two Queries. Is the data type in both queries a numeric type? Is there set to text type?

Please Group By the SKU to check the number of SKU.

vpollymsft_0-1643191120485.png

 

 

vpollymsft_0-1643189838281.png

If there still some errors, please provide a relevant screenshot so that we can identify the issue.

 

 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

AlexisOlson
Super User
Super User

Bear in mind that duplicates in the query editor and duplicates in the model aren't exactly the same since M is case-sensitive and DAX is not. Is it possible that you have some SKUs that differ only by case?

 

Try clean, trim, and upper case and then remove nulls and remove duplicates on the SKU column.

 

Text.Upper(Text.Trim(Text.Clean([SKU])))

 

 

Thank you Alexis, I couldn't figure it out!  Yes, it was due to M being case-sensitive and DAX not, and your solution worked perfectly.

I had the same issue and this worked for me, thanks a lot for the explanation!!

Hi! Thanks for the tip - unfortunately, no, this can't be the problem, as all the SKUs are numeric only, which can't have case sensitivity. 

Hi @Thigs, try to do the following:

  1. Load your data to PowerBI
  2. go to "Table view", select the table you need and look at column profileSergii24_0-1712236106631.png
  3. Do you have the same value in number of rows for "Table" and SKU "column"? If not, create a table visual with 2 columns: SKU and Count SKU, then sort it in descending order by SKU number to find the duplicate

If it doesn't help, please send us some statistic of your both dimensional and fact tables as well as details of the column you use as a key (like in the screenshot above).

Could it be that you have any other relationship in your data model that doesn't allow you to create one-to-many relationship? 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.