Forum Discussion

jauzmendi's avatar
jauzmendi
Frequent Visitor
3 years ago
Solved

JOINING TABLES WITH MULTIPLE VALUES (Many to Many relationship)

Hello, 

 

I am trying to Join two tables that do not have unique identifiyers but I am getting many errors. These are the tables: 

Table 1:

Son CodeParent code
Code 1Code A
Code 2Code B
Code 2Code C
Code 3Code D

 

Table 2: 

Son CodeSale amount
Code 145
Code 235
Code 215
Code 146
Code 348

 

As you can see, If i connect both tables, by "Son Code" I will get a many to many relationship. What i am willing to get is a third table having the "Code 2" rows duplicated in order to identify all the "Parent Code"s I have in the table 1 for each "Code Son" in the table 2. 

 

This should be the result (In red the new rows):

Table3:

Son CodeSale amountParent Code
Code 145Code A
Code 235Code B
Code 235Code C
Code 215Code B
Code 215Code C
Code 146Code A
Code 348Code D

 

In MS Acess I was able to get it by doing a simple LEFT JOIN. 

 

Thank you

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi jauzmendi 
    Please refer to attached sample file with the proposed solution

    Table3 = 
GENERATE ( 
    Table1,
    SELECTCOLUMNS ( 
        FILTER ( 
            Table2,
            Table2[Son Code] = Table1[Son Code]
        ),
        "Sales Amont", [Sale amount]
    )
)

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi jauzmendi 
    Please refer to attached sample file with the proposed solution

    Table3 = 
GENERATE ( 
    Table1,
    SELECTCOLUMNS ( 
        FILTER ( 
            Table2,
            Table2[Son Code] = Table1[Son Code]
        ),
        "Sales Amont", [Sale amount]
    )
)
    • jauzmendi's avatar
      jauzmendi
      Frequent Visitor
      3 years ago

      First of all thanks for response! The idea is very very good!

       

      Although it is helpfull,I actually need to do i the other way. Instead of replicating Table 1 and include the Sales amount, I need to Replicate Table 2 and include Parent Code. 

      I can use your idea and include one by one all the columns from Table 2 but it's not the best idea I think . 

       

      Thanks

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        jauzmendi 

        Exactly. It is the same idea

        Table3 =
        GENERATE (
        Table2,
        SELECTCOLUMNS (
        FILTER ( Table1, Table1[Son Code] = Table2[Son Code] ),
        "parent code", [parent code]
        )
        )