Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Combining 2 tables based on unique values

I have a question and I need to combine 2 datasheets (2 different tables) based on unique values is both columns.

Both tables have the column with the unique value SearchCode and however 95% of the SearchCodes are in both tables what I really need is to get that 5% which is in Table A and/or B.

I've tried the summarize function but that only gets the distinct values from either table A or B, i've tried the advanced editor to merge both but that doesn't work because it's a summarized table.

 

Need Help

7 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, RonaldvdH 

    here my data look like

    table 1

     

    table2 

     

     


    try below code for new table

    new table=
var table_1 = SUMMARIZE(Table1,Table1[id],Table1[name])
var table_2 = SUMMARIZE(Table2,Table2[id],Table2[name])
var result = EXCEPT(table_2,table_1)
return
UNION(table_1,result)

     

    final result

     

     

     

     

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    Dangar332 Ive tried your option but it returns an error that the function EXCEPT doesn't support the joining of a column fromt the type TEXT with a column with the type INTEGER

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      Hi, RonaldvdH 

       

      For combining two tables you have same column type in both tables

      Here you got error because of one of your table's column is integer and another table's column type is text 

      So make sure both tables has same type of column that you want to merge 

      • RonaldvdH's avatar
        RonaldvdH
        Post Patron
        2 years ago

        That did the trick but the new table has duplicate rows so how do I just get the unique values ?

         

        Edit: the SearchCode is in both tables the same but some columns have a different value in table B then it has in table A and viceversa. Is it possible for the EXCEPT to just check based on the SearchCode ?