Lookup Multiple Values from Another Table & Concatenate with ; as delimeter

[apmulhearn]

Hello,

I am working with two distinct tables which contain information about trips. I need Table2.AllEmailAddresses to contain all distinct matches from Table1.EmailAddress where the Status is Booked. It should ignore any other Status values. If an email address is used multiple times for a DepartureID, that email address should only be included once. Sample Image below with "AllEmailAddress" in Table2 completed with ideal result.

Many thanks for any help.

[Anonymous · Accepted solution]

Hi @apmulhearn ,

You can create a calculated column as below in Table2 to get it:

Column = 
CONCATENATEX (
    FILTER (
        'Table1',
        'Table1'[DepartureID] = 'Table2'[Departurel]
            && 'Table1'[Status] = "Booked"
    ),
    'Table1'[EmailAddress],
    ";",
    'Table1'[EmailAddress],
    ASC
)

By the way, why the email address “stan@fakeemail.com” not be listed under the Departurel as "LGA:356985895"?

Best Regards

[parry2k]

@apmulhearn assuming Table2 has a relationship with Table1 on DepartureID column which will one to many relationship, one will be on Table2 side and many will be on Table1 side, add a new column in Table2 with the following expression:

Email Address = 
VAR __table = CALCULATETABLE ( RELATEDTABLE ( Table1 ), Table1[Status] = "Booked" )
RETURN

CONCATENATEX ( SUMMARIZE ( __table, [DepartureId], [Email] ),  [Email], ";" )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@apmulhearn could you please clarify further what you are looking for? Not sure what the ask is.

[apmulhearn]

Sure, I can try again.

Basically, I need to be able to lookup values in table 1 and have them populate a column in table 2. However, there will often be more than 1 match on table 1. I need all matching values from table 1 to populate in the column in table 2, separated by a semicolon.