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 ...
Need Help
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 ?

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, RonaldvdH 

    yes, it possible just compare it with search code like below

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

     

    it give only join of search column

     

    if you still not get your answer then provide some sample data and expected output

    • RonaldvdH's avatar
      RonaldvdH
      Post Patron
      2 years ago

      Dangar332 maybe I need a differnt approach ....

       

      I can summarize both tables based on your formula but then I only have 1 column with SearchCodes (Unique values from both Table A and B)

      But then I only have that column but I need the other columns as wel.

       

      However this formula just checks values in table A but what I need is a formula that first searches table A and if that returns a blank then checks table B.

      Can I change the varStreet formula so that it doesn just that ?

      Straatnaam = 
VAR varSearchCode = Test[Searchcode]
VAR varStreet = FILTER(ALL('Adressen'),Adressen[Searchcode] = varSearchCode)
RETURN
MAXX(varStreet,Adressen[straat])

       

       

      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        Hi, RonaldvdH 

        plese provide data and expected output