Look up tables

[Alessandro-laba]

Hi there, 

In my Power BI desktop i have 2 different sources of data, one is coming from a simple excel file while the other one is coming from our marketing platform Eloqua. In both set of data i have a column for Email addresses but their ID is not matching cause these 2 systems are not integrated. Now what I would like to do would check if an email address is present in both tables and in case giving a numerical value for that in order to have only one ID for both email addresses. 

As an example please see below: 

Data source 1

Data source 2

Outcome: 

As you can see in the above image only the email3 and email5 were present in both files and so I gave the ID present in the 'Data Source 2' to the 'Data source 1'

Is it possible doing that in Power BI and if yes would anybody be able to help me?

Thanks,

Alessandro

[jdbuchanan71]

@Alessandro-laba 

Would it work if you build a list of all the unique emails from both tables then join it to the tables?

Emails = 
DISTINCT(
    UNION(
        DISTINCT(Table1[Email]),
        DISTINCT(Table2[Email])
    )
)

 

Then you can just use the email from the Emails table.

[Alessandro-laba]

Hi there, 

Actually i have already something like that in place, the 2 tables are actually linked to each other. However i would like to have a numeric value cause in the future I would like to build a Venn diagramm and from my understanding I need numerical values to do that.

Thanks, 

Ale 

[jdbuchanan71 · Accepted solution]

You can use LOOKUPVALUE to get the ID from table 2 onto table 1

ID = LOOKUPVALUE(Table2[ID],Table2[Email],Table1[Email])

This will pull the ID from table 2 onto table 1 only where the email matches.

 

My ID's start at 1 just bacause I assigned an index column for the example.