Error in loading from Query to Model on table containing duplicates

[Anonymous]

Hi, I encountered in issue in loading a table to the model. I brought in a CRM table in the query which contains duplicates in one of the columns (the whole table is not a duplicate just the one column because there are multiple descriptions to a single product ID and those descriptions need to be broken down into indvidual rows for mapping purposes).  When I tried to load this to the model it came up with an error message saying "Could not load as found duplicates in product ID". I removed the product ID column from my query and successfully loaded to the model, then went back to the query and deleted the step of removing the product ID column and loaded it back to the model successfully. I have since tested several dashboards and gone through the same step of loading the model with the product ID (which continues to contain duplicates) and the query has loaded successfully without me having to go through the additional step of removing the column and adding back in.

Can anyone explain to me why this could have happened, and also why I could not replcate the issue? My understanding was that powerBI would allow for loading duplicates but it you would not be able to build relationships successfully. I am worried that any auto-refreshes once I publish might cause the query to fail at some point. This particular dashboard has to be refreshed with CRM data every 30 minutes. Unfortunately I don't have a screenshot of the error message.

[v-yuezhe-msft]

@samanthasampath ,

What is the full error message do you get? If you get the error message like below, you would need to delete the relationship and re-create the relationship as Many to Many after loading data.

Column ' ' in Table ' ' contains a duplicate value and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

Another method is to disable autodetect relationship option in your PBIX file.


Regards,
Lydia

[Anonymous]

Thank you for responding Lydia, that was indeed the error message I got. There was no pre-existing relationship for the table I was bringing in, I couldn't enable the initial load to the model in order for any relationship to be formed, so why did that error message come up? If I use the function to disable the auto-detect relatioships will that prevent this error from happening even if it's a completely new table I'm bringing in?

[v-yuezhe-msft]

@Anonymous ,

Could you please share sample data of your tables so that I can test? After disabling auto-delect relationship option, you can re-create relationships  between tables manually. As long as updated data in your columns of each table meets the requirement of the relationship you create, you will not get this error message.

Regards,
Lydia