Create Relationship

[Babinsky]

Hi everyone, I have a question - I'm getting the following error message.

You can't create a relationship between these two columns because one of the columns must have unique values.  

These two columns are date fields.  One is week format is other dat format, so I understand the confilct.  

Does anyone have any suggestions?  Thanks!

Babinsky

[TedPattison · Accepted solution]

In the tabular database model, you can only create a relationship between two tables when at least one of the columns being used to create the relationship has unique values. It seems like both of your date fields have duplicates values.

You need to deteremine which of these two date columns in your two tables should have a unique values. Then use the Power BI Desktop query tool to de-duplify these duplicate date values so that the query outputs a table with unique column values. After that, you should be able to create the relationship.

[Babinsky]

Thanks for the suggestion Ted.  Question - how do you de-duplify these duplicate date values?

[TedPattison]

First, make sure that the column in question is based on Date and not DateTime when you examine it in the query edito window. Next, you use the Group By step so that every row withthe same date is collapsed into the single row.

Is this table you are working with a specialized Calendar table?

[Babinsky]

Thanks for suggestion.  Do you or anyone know how to remove duplicate rows in Power Bi so the fields are unique?

[Eric_Zhang]

@Babinsky

You can remove the duplication in Power Query. Check a similar thread.