Getting Random Duplicates after Merge and Expand

Question

I'm using Merge (left outer) on two fields with Expand column, my right side is unique (Check with SQL including case sensitive collation).

Before I expand I don't get duplicates, after I expand I get duplicates, however the duplicates rows are identical (using all columns from right side) and appear to be random as after refreshing the table I get different duplicates each time.

I have also sorted data in Power Query as final step, and when I look at the duplicated ID, it actually isn't duplicated, but is duplicated in Power BI Report.

Applying remove duplicates solves the problem, but I'm trying to understand why merge with expand would produce duplicates. The table is 100k+ rows.

Any ideas?

Regards

Emil

_emil_ · Answer

Hi&nbsp;&nbsp;I have managed to&nbsp;confirm it's an issue.&nbsp;I have exported my "item" dimension to a CSV and "swapped" Power BI source. The issue goes away.&nbsp;I put back SQL and issue returns and is still random.&nbsp;On 10 runs (refreshes with same the same data) I get:1) Left side duplicates + blanks2) no issue3) no issue4) Item dimension contains duplicates (which it doesn't)5) no issue6) no issue7) Left side duplicates + blanks8)&nbsp;Item dimension contains duplicates (which it doesn't)9) No issue10) No issue&nbsp;*&nbsp;(blanks between 670+ to 50k out of 135k unique rows, duplicates between 5k to 30k depending on the run)&nbsp;The only difference I see is different collation left side&nbsp;Latin1_General_CI_AS right side (item dim)&nbsp;Latin1_General_CI_AS&nbsp;I have&nbsp;considered collation when I started having the issue but I don't understand what it is random.&nbsp;I've tried using collate to match the other side (tried on both sides), but it didn't work.&nbsp;Is there a collation setting in Power BI when SQL Server is used as a source?&nbsp;RegardsEmil

v-huizhn-msft · Answer

Hi&nbsp;_Emil_,The date in left side have duplicate, right? If it is, I try to reproduce the scenario, please review the following.The first table is left side table, the second is right side table which is unique.I merge(left outer) on two tickets, and result as the screenshot below.From the result, I get duplicates after expand. This is because there are duplicates, the value in right site must matches them.If you have any other issue, please feel free to ask.Best Regards,Angelia

_emil_ · Answer

Hi Angelia,&nbsp;Let me clarify, using your example you have 5 rows on left side, and you get 5 rows as final result, in my case I get 5+ rows, even when right side is unique.&nbsp;So my output is like this:1. D, D2. D, D3. C, C4. E, E5. E, E &lt;-- duplicated6. F, NULL&nbsp;and if I refresh data my duplication would be different like this:1. D, D2. D, D3. C, C4. C, C&nbsp;&lt;-- duplicated5. E, E&nbsp;6. F, NULL&nbsp;I have this beheviour on several different merges now, in all cases right side is unique, so I don't expect to get more rows on left side, but I do.&nbsp;&nbsp;&nbsp;If I take my duplicated row and filter my left side (1 unique row) than I no longer get the duplication,&nbsp;and if I was doing something wrong (which is possible) I would expect it to be there duplicated.&nbsp;I'll try to play with it a bit more to see if I can narrow down the issue.&nbsp;RegardsEmil

v-huizhn-msft · Answer

Hi _Emil_，For your requriement, it should change dynamically. We are unable to achieve your expected result. Thanks for your understanding.&nbsp;Best Regards,Angelia

_emil_ · Answer

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).&nbsp;I'll try to narrow it down and see if it is something on my side that I'm misunderstanding.&nbsp;&nbsp;RegardsEmil&nbsp;

Forum Discussion

Getting Random Duplicates after Merge and Expand

7 Replies

Related content

Random Refresh Fail

Change random dates

Duplicate ranking

Non volatile random numbers between

Generate random list without duplicates