I have two tables from two different databases. both connected with Date table, but these two tables has no direct relationship.
table 1 from sales database, table 2 from contact and leads database.
both tables have email columns which are actual buyer’s email and prospect buyer’s email. I am trying to compare these two email columns and see how many emails are matching, and count this number, then calculate the percentage of the count out of sales, so i will know eventually how many prospect buyer turn into a real buyer over time.
I am looking for a best way to achieve this, thank you for the help!
You should be able to do all this in the query editor quite easily.
You need to create a new tables for each of these email lists, then you could probably complete a merge and you’ll see quickly how many match up.
You probably also want to create a master list of customers as a new lookup table. This way you can have this table join both of your fact tables.
(there should never be any relationships between two fact tables)
See here for relevant tutorials around how to complete a lot of this.
Highly recommend going through this entire course as I feel if you do you’ll be able to solve for what you need pretty quickly as it mainly a modelling problem rather than a formula one.
Here’s my process, I created a new email table from the “Prospect contact” table which is called “contact” in the data model, I name the new table as “Lead contact” which only contains “email” and a new column with text “match”.
I was trying to join/merge this “lead contact” table on the “actual buyer” table (it called “lot buyer” in data model) by using the “email” column, so it should based on the identical email to show whichever matched the actual buyer email, then I can count how many matches out of the total number of actual buyers, it is the answer i was looking for.
However, in the power query, the calculated “lead contact” was not showing in any table list, so I was not able to merge to the “lot table”
Before you go any further you need to sort out your model.
Please go through this course as soon as possible.
Without the right setup in you model you will always come across issues that seem difficult to solve when in reality if you have got the structure correct in your model everything should just happen seamlessly.
The biggest problem here is that you don’t have a clear delineation between your tables, lookup vs fact tables. You need to be creating a lookup table of these emails or a customer lookup table.
This will all become clearer once you work through all the best practices in the course above.
