Relationships between large datasets

[harryrhulme]

Hello,

I am trying to create a relationship table between two large data sets. I have created a relationship table containing unique data, no blank rows and there are over 30,000 data entries. I am getting an error when trying to create the relationship in Power BI saying that I haven't removed duplicate values even though I have. Is this because the key table containing unique data is so large and can anyone offer a solution?

Many thanks

[v-chuncz-msft]

@harryrhulme,

First, you may select "Remove Duplicates" in Query Editor.  

Second, check uppercase/lowercase spelling if it is a text field. Text(such as a, A) in query editor is case-sensitive, but not in the data model.

Finally, use SUMMARIZE Function and COUNT Function to have a check.

[harryrhulme]

Thank you that has solved the problem for some duplicates.

I am still getting a problem with some data being duplicated even though I have formatted into lowercase - removed duplicates and uppercase - removed duplicates. Do you have any other suggestions as to the formatting issues that could be causing this?

Thank you

[v-chuncz-msft · Accepted solution]

@harryrhulme,

Check rows in the following calculated table.

Table =
FILTER (
    SUMMARIZECOLUMNS ( Table1[Column1], "Count", COUNTROWS ( Table1 ) ),
    [Count] > 1
)