cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bdmichael09 Regular Visitor
Regular Visitor

Forced to use many to many relationship

I have two tables, one is a product list table. It is nothing more than a list of a product identifier along with product information. Below are some of the columns just as an example.

 

image.png

 

I have also have my data table. In the data table I have a Product column. When I try to create a relationship between these two tables, Power BI defaults to a Many-to-Many relationship in what I believe should be a one-to-many relationship because there is should only be unique values in the Product column of the Product list.

 

image.pngimage.png

 

First I checked my Product list to make sure that there weren't any duplicates in there somehow that would cause this. Clearly there are not.

 

 image.png

 

There are duplicates of the product in the data table, but that is expected as different products will be used numerous times. My problem is I am trying to use a Related() function to pull in information from the product list to the data table based on the product but I get an error.

 

image.png

 

Has anyone else ever encountered this or have any suggestions on how to correct this issue?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: Forced to use many to many relationship

@bdmichael09

The only thing I could think of is perhaps you have null value in your product list. 

8 REPLIES 8
NickNg278 Senior Member
Senior Member

Re: Forced to use many to many relationship

@bdmichael09,

Can you provide the full data model? There might be some existing relationships that invalidate your product data and product list tables. 

bdmichael09 Regular Visitor
Regular Visitor

Re: Forced to use many to many relationship

@NickNg278 This is the full model to this point. I have a date table with a relationship to the data table. The bad relationship with the data table and the product list. The data table has a relationship with the employee data table. The employee data table has a relationship with the leaders table which is just the organizational hierarchy. Everything should be a one-to-many relationship. There isn't anything out to the ordinary going on in terms of relationships so I'd be surprised if a different relationship was causing the issue. 

 

image.png

perezmarcone Regular Visitor
Regular Visitor

Re: Forced to use many to many relationship

use the cross filter to change the cardinality to 1 to many.

bdmichael09 Regular Visitor
Regular Visitor

Re: Forced to use many to many relationship

@perezmarcone Would that I could. When I tried to do that it gave me the error that the cardinality isn't valid for the relationship. This is why I first went to check for some kind of involuntary duplicate Product in my Product list. To me, the only way I could have a many to many is if there were duplicate Products in the Product list. Since I know there aren't, there has to be something else going on that I just don't understand that is forcing Power BI to think it has to use this many to many rather than the one to many relationship that I want. 

NickNg278 Senior Member
Senior Member

Re: Forced to use many to many relationship

@bdmichael09

The only thing I could think of is perhaps you have null value in your product list. 

perezmarcone Regular Visitor
Regular Visitor

Re: Forced to use many to many relationship

 Do a sql code to remove null values, if it does not work, create a bridge table, containing the id of the product. and then join both tables to the bridge for a 1 to many.

Highlighted
bdmichael09 Regular Visitor
Regular Visitor

Re: Forced to use many to many relationship

One of the product values in the table was null. Not sure who's bright idea that was (I had no hand in making the product list) but I didn't even think about it. Appreciate it!

mattmalone Frequent Visitor
Frequent Visitor

Re: Forced to use many to many relationship

Hi all,

 

I've read through this and while I have the same issues as the original poster, I cannot seem to find what is causing my issue.

 

No doubt I'm going something wrong as today is the first day that I'm really working with Power Query.

 

  • I've looked to filter out Null values in my Product List. The Product list contains the unique key field.

    = Table.SelectRows(#"Removed Columns", each [StockCode] <> null and [StockCode] <> "")

    I also created a calculated field to run an If, Then to see if anything came up positive for a null value. No positive results.

  • I've run another query to see if there are any duplicate values by Transform -> Group By ->Stockcode Count. No values greater than 1.
  • My only other guess (as I'm new to Power Query) is if there is an issue with a particular operator (@, for example) and if there are particular operators that may impact on being able to create a many-to-one relationship.

  • The transaction Table will have duplicate product codes and have duplicate order numbers. This is obviously the many side of my relationship and I couldn't think why this would impact on anything. I added an Index just for fun but it's still suggesting that the relationship between the two tables is a many-to-many, not a many-to-one.

It's my first day getting really into Power, and I'm ssooo close yet sooo far!

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 426 members 4,066 guests
Please welcome our newest community members: