Can't Create Relationships - missing intermediate data to connect the two columns

[kfschaefer]

I am attempting to create a relationship between the MainData Table and GroupDetail Table via the GroupNumber & Group Name.  However I am getting the following issue:

Why?

Thanks!

[varakcheev]

PowerBIDesktop shows this message when it detects a Many-Many relationship (which are not supported). Basically at least one of the columns must have unique non null values.

Please doublecheck that the GroupDetails[GroupName] column doesn't have duplicates or nulls. (BTW comparison is case insensitive so "GROUP 1" and "group 1" are considered a dupe). If you find a dupe/null you can filter it out in the "Edit Query" view and retry to create the relationship.

[mork]

Usually this happens because one of your columns doesnt have all the data that the other has. Another requirement of the relationship function though is to not have any blanks in either of the two columns that you are trying to relate and also only one of the two columns is allowed to have the same value multiple times. One of the two columns always has to have each distinct value once.

[Sean]

@kfschaefer You are missing a [GroupName] in the Table that contains the unique values of GroupNames which looks to be GroupDetail table.

Meaning in the table on the MANY side you have at least one [GroupName] that does not exist in the table on the ONE side.

MainData table                  GroupDetail table

GroupName Column          GroupName Column

GroupName 1                     GroupName 2

GroupName 1                     GroupName 3

GroupName 2

GroupName 3

GroupName 3

[dejan]

Try to copy (duplicate) GroupDetail table, then remove all columns from this table except GroupName. Then remove duplicates. This new table that will contain all unique values for GroupName can be used as your intermediate table to connect GroupDetail and MainData tables. Hope this helps.

[dejan]

In this case, maybe it will be better to use MainTable since it contains all GroupNames....

[Seth_C_Bauer]

@kfschaefer Maybe I'm not understanding something... The relationship would be on Group ID in both tables... Is this the case, and your description makes it sound like you are trying to create a relationship from name to ID...