Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |