Duplicate Values

[PowerBI123456]

I am getting an error in Power BI service saying I have a duplicate value in one of my keys that is a 1 to many relationship. In power query, I removed all duplicates and nulls. I even looked up the value they said its duplicate and I am only finding one. Any idea why this is happening? 

[PowerBI123456]

@parry2k @smpa01 @Pragati11 Thanks all! I figured it out. It was the way my incremental refresh was set up

[parry2k · Accepted solution]

@PowerBI123456 Reason I brought that up, in case you have partial data in desktop and during incremental refresh, you have full data load and that's when there is a duplicate /blank value and causing the issue in the service and not in the desktop since in desktop you are seeing partial data.

If you are using incremental refresh, you check at your backend (not sure what is your data source) and see if you are getting any duplicate values, maybe run the query against the full dataset. As we know there can be many reasons for it, you have to look at the full dataset to identify the problem.

I would also recommend taking the actions recommended by @smpa01 

[smpa01]

@PowerBI123456can you try running a TRIM and CLEAN in PQWRY on the key column after removing duplicates and see if it improves?

[parry2k]

@PowerBI123456 hmmm, are you doing incremental refresh, or something changing in service, like data source or in the desktop you are loading partial data compare to what gets loaded in Power BI service.

Have you done a full refresh on the Power BI desktop to test it?

[PowerBI123456]

@parry2k Doing incremental refresh in service. Is that what is causing it? 

[parry2k]

@PowerBI123456 that is weird, has to be blank or empty. I hope you don't have a filter on the page that is filtering the rows you are seeing in table visual.

[PowerBI123456]

@parry2k No page filters. Its weird because it loads in Desktop with no issues, but runs into an issue in service only.  

[parry2k]

@PowerBI123456 in a scenario like this I do one thing, put the id column and count of the id column in a table visual, sort it by count, and see if there is anything that shows up more than once, also sort on id to check if there is anything blank or empty.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PowerBI123456]

@parry2k I did that and everything is 1 count and no blanks. 

[Pragati11]

Hi @PowerBI123456 ,

If the error is still there then there is duplicate value in key column for sure.

Can you share your data model? Or if possible the pbix file by removing any sensitive information.

Thanks,

Pragati

[PowerBI123456]

@Pragati11 Unfortunately I can't share, its all PHI. 

[Pragati11]

Hi @PowerBI123456 ,

But there has to be some blanks or duplicate values. As @parry2k suggested, can you check if you have any filters on your report which is not showing you this data?

Thanks,

Pragati