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.
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.
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.
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.
Has anyone else ever encountered this or have any suggestions on how to correct this issue?
Solved! Go to Solution.
I ran into this again tonight, for more than the first.
I've ignored it before and it didn't seem to cause too much of an issue but I was stubborn this time. The only way I had around it was to find the values which were still being duplicated, even after a Remove Duplicates in the query editor.
I had about 7K companies in the DIM table but 16 or them kept returning a second value. Only way I could do it was to hard code the 16 of them in a filter. As the editor will only give you a preview of the first 1,000 values, you'll hard code the values into the code.
Not pretty but it works.
Greetings from New Zealand,
I just had the same issue and couldn't find any duplicates, nor null values in my Excel file.
However after importing my Excel file into Power BI I noticed that Power BI had imported two more rows at the end than I expected from my Excel file and thus created Null value rows.
It turns out that Power BI can import additional Null value rows from Excel.
The safe solution to prevent this is
to change the data in Excel into an EXCEL-TABLE first (mark the range > Insert > Table)
then import it to Power BI
This way no more null rows are created and the relationship 1:Many is possible.
When working with strings, another issue that may confuse Power Query is if there are any unrecognized characters or extra spaces in your strings. Running the TRIM and/or CLEAN transformation functions addresses those issues.
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.
It's my first day getting really into Power, and I'm ssooo close yet sooo far!
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!
@Anonymous 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.
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.
@Anonymous 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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.