Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Relationship between two tables with repeated values

Hi guys, I have two tables that I need to establish a relation but I`m having some trouble. If anyone could help me I would be very grateful.

 

01. The first one called "Disparo" with the columns "Lote", "Date" and "Email", where each day I have a new "Lote" number and the same email range, like this:

LoteDateEmail
98DJSH4723HMMay 15[email protected]
98DJSH4723HMMay 15[email protected]
98DJSH4723HMMay 15[email protected]
53KJS38R7FNK3May 16[email protected]
53KJS38R7FNK3May 16[email protected]
53KJS38R7FNK3May 16[email protected]
 

02. The second one its called "Respostas" and has the columns "ID", "Lote", "Email" and "Nota", where each new "Lote" I have the same group of "Email" but with different "ID" and "Nota" that can be 1 or 2, like this:

IDLoteEmailNota
2678551998DJSH4723HM[email protected]2
2548522398DJSH4723HM[email protected]2
1398470998DJSH4723HM[email protected]1
3295579953KJS38R7FNK3[email protected]2
3896480153KJS38R7FNK3[email protected]1
4599775853KJS38R7FNK3[email protected]2
 

Now what I need to do is to "pass" the column "ID" from Table 02 to Table 01 so I can create a relation between them and get the "Nota" column according to "Email" and "Lote" (because ID is the only column with unique values since "Lote" and "Email" repeats). I can't do a LOOKUPVALUE with "Email" because I have the same emails in every "Lote".

 

Any suggestions? Thank you very much.

Need Help

3 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Anonymous -

    Are you opposed to merging both tables with a join in Power Query? Edit - this does essentially the same thing as a compound key

     

    let
    Source = Table.NestedJoin(TableOne, {"Lote", "Email"}, TableTwo, {"Lote", "Email"}, "TableTwo", JoinKind.LeftOuter),
    #"Expanded TableTwo" = Table.ExpandTableColumn(Source, "TableTwo", {"ID", "Nota"}, {"TableTwo.ID", "TableTwo.Nota"})
in
    #"Expanded TableTwo"

     

     

    The other option I can think of is creating a compound key with your existing columns.