Forum Discussion

DataGuy2016's avatar
DataGuy2016
Advocate IV
9 years ago
Solved

Table Merge creates duplicate records

Hi Folks,   I am stumped.   I am trying to merge two tables into a new table using a LEFT JOIN.  Both tables have unique records on each row.  When I join the tables, BI creates duplicate rows on...
Need Help
  • DataGuy2016's avatar
    DataGuy2016
    9 years ago

    The problem lay with the Data.

     

    The LEFT JOIN functionality of PowerBI works perfectly.

DataGuy2016's avatar
DataGuy2016
Advocate IV
9 years ago

Hi there,

 

I am running the merge again and it is still creating duplicate records from the LHS table.  The LHS table has unique rows of data.  So I have 183,000 records merged into 187,000 records post join.

 

I cannot understand why BI would create an exact duplicate of the LHS row in the merged table?  Could it possibly be because the primary key is duplicated?  I know that this is not scientifically viable however this is the closest I can get to a primary key.

 

 

 

J_R_Cribb's avatar
J_R_Cribb
Helper I
8 years ago

Hi DataGuy2016, 

 

If you haven't already found a solution...

 

I had a similar issue where LEFT JOIN was creating duplicates, resulting over 1000 rows of data where I should have just 85.

 

I solved the issue by expanding the relative columns as an aggregate, instead of just an expansion.

 

Hope that this makes sense.

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I am having the same problem with my data.  And no, I do not understand your answer to "expand as an aggregate," will you please clarify?

     

    I know this is an old thread, but if someone else is still reading this thread and would like to offer help, I appreciate it!

     

     

    • J_R_Cribb's avatar
      J_R_Cribb
      Helper I
      8 years ago

      Hi Anonymous, 

       

      I have created a quick sample that I hope helps explain my method. It may not work with your data, but it did fix the issue with mine!

       

      I have two tables, I would like to merge 'Customer Type' with 'Customer Name'.

       

      Customer Name Table

       

      Customer Type Table

       

      I would like to merge using the 'ID' column in both, to bring in the 'Cust Type' field from table 'Customer Type' into table 'Customer Name'.

       

       

       Merge Options

       

       

      Once successfully merged, I see the below:

       I now have the option to expand that column of the merged table to bring in data from the 'Customer Type' table. But this is where I choose to Aggregate a field rather than expand. You may require a different aggregate measure other than median, dependant on data type:

       

      Expand as Aggregate

       

      The result from my data is below:

       

      Result

       

      Please also see sample.pbix file which I hope can help you with your issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HI JR,

       

      Thank you for the response.  Could you send me an email at [email protected].  I want to talk with you more and share a sample file, but I can't figure out how to upload an excel file here.

       

      Thanks,

       

      Tera