Forum Discussion
Getting Random Duplicates after Merge and Expand
Thanks, it seems to happen only with larger volume of data, and affects only very small % of rows, but appears with almost every merge I use that uses different logic, columns (no issues with SQL Left join).
I'll try to narrow it down and see if it is something on my side that I'm misunderstanding.
Regards
Emil
Hi
I have managed to confirm it's an issue.
I have exported my "item" dimension to a CSV and "swapped" Power BI source. The issue goes away.
I put back SQL and issue returns and is still random.
On 10 runs (refreshes with same the same data) I get:
1) Left side duplicates + blanks
2) no issue
3) no issue
4) Item dimension contains duplicates (which it doesn't)
5) no issue
6) no issue
7) Left side duplicates + blanks
8) Item dimension contains duplicates (which it doesn't)
9) No issue
10) No issue
* (blanks between 670+ to 50k out of 135k unique rows, duplicates between 5k to 30k depending on the run)
The only difference I see is different collation left side Latin1_General_CI_AS right side (item dim) Latin1_General_CI_AS
I have considered collation when I started having the issue but I don't understand what it is random.
I've tried using collate to match the other side (tried on both sides), but it didn't work.
Is there a collation setting in Power BI when SQL Server is used as a source?
Regards
Emil
Yes, I see the same thing using SQL Server as the source. It's also possible to connect to SQL Server via OLE DB (I haven't checked to see if this resolves the issue). I am just removing duplicates twice, as you said in your first post.
