How to Combine Xero and HubSpot Data in Power BI Using Connectorly

Step 8: Add Automatic Email Matches

Exact name matching will not find every customer. HubSpot and Xero may contain different company names, abbreviations or legal suffixes. An exact email match provides a useful second method.

This process matches hubspot contacts[Email] with xero contacts[Email Address]. The HubSpot contact’s Company ID then identifies the corresponding HubSpot company and Customer Key.

Individual customers: This example matches Xero contacts to HubSpot companies, which is suitable when the customer is an organisation. However, a Xero contact may represent an individual—for example, when an invoice is raised to a person rather than their company. In these cases, repeat the same name and email matching approach using hubspot contacts[Contact Name], hubspot contacts[Email] and hubspot contacts[Contact ID]. A contact-level Customer Key can be created using the HubSpot Connection Name, the word CONTACT and the Contact ID. Do not force an individual customer to a HubSpot company when no appropriate company association exists. 

Create an email normalisation function

1. In Power Query Editor, select Home > New Source > Blank Query.
2. Rename the new query fxNormaliseEmail.
3. Right-click fxNormaliseEmail and select Advanced Editor.
4. Remove the existing content and paste:

(value as nullable text) as nullable text =>
let
    Cleaned =
        if value = null
        then null
        else Text.Lower(Text.Trim(Text.Clean(value)))
in
    if Cleaned = ""
    then null
    else Cleaned

Select Done. The function converts email addresses to lowercase and removes unnecessary spaces and non-printing characters. It does not remove characters such as @, full stops or hyphens.

Prepare the HubSpot email candidates

1. Right-click hubspot contacts and select Reference.
2. Rename the new query HubSpot Email Candidates.
3. Keep these columns:
   • Connection Name
   • Company ID
   • Email
4. Filter Company ID to remove null values.
5. Open Add Column > Custom Column.
6. Name the new column Customer Key and enter:

if [Company ID] = null then null
else Text.From([Connection Name]) & "|" & Text.From([Company ID])

7. Set Customer Key to the Text data type.
8. Open Add Column > Invoke Custom Function.
9. Name the new column Normalised Email.
10. Select fxNormaliseEmail as the function.
11. For the value parameter, select Email.
12. Select OK.
13. Set Normalised Email to the Text data type.
14. Filter Normalised Email to remove null and blank values.

Allow duplicate contacts belonging to the same company

The same email address may appear on duplicate HubSpot contact records that belong to the same company. This does not make the customer match ambiguous.

1. Select Normalised Email.
2. Hold Ctrl and select Customer Key.
3. Open Home > Remove Rows > Remove Duplicates.

This leaves one row for each unique combination of email address and HubSpot customer.

Exclude emails belonging to several HubSpot companies

1. Open Home > Group By.
2. Select Advanced.
3. Group by Normalised Email.
4. Create an aggregation named Rows using All Rows.
5. Add an aggregation named Match Count using Count Rows.
6. Select OK.
7. Filter Match Count so only rows equal to 1 remain.
8. Expand Rows and select:
   • Connection Name
   • Company ID
   • Customer Key
9. Clear Use original column name as prefix and select OK.

This prevents a shared email address from automatically linking one Xero contact to several HubSpot companies.

Prepare the Xero email candidates

1. Right-click xero contacts and select Reference.
2. Rename the query Xero Email Candidates.
3. Keep these columns:
   • Connection Name
   • Contact ID
   • Contact Name
   • Email Address
4. Open Add Column > Invoke Custom Function.
5. Name the new column Normalised Email.
6. Select fxNormaliseEmail.
7. For the value parameter, select Email Address.
8. Select OK.
9. Set Normalised Email to the Text data type.
10. Filter Normalised Email to remove null and blank values.

Exclude duplicated emails within each Xero organisation

1. Open Home > Group By.
2. Select Advanced.
3. Group by Connection Name.
4. Select Add grouping and add Normalised Email.
5. Create an aggregation named Rows using All Rows.
6. Add an aggregation named Match Count using Count Rows.
7. Select OK.
8. Filter Match Count so only rows equal to 1 remain.
9. Expand Rows and select:
   • Contact ID
   • Contact Name
   • Email Address
10. Clear Use original column name as prefix and select OK.

Create the exact email matches

1. Select Home > Merge Queries > Merge Queries as New.
2. Use Xero Email Candidates as the first table.
3. Use HubSpot Email Candidates as the second table.
4. Select Normalised Email in both tables.
5. Choose Inner as the join kind.
6. Make sure Use fuzzy matching to perform the merge is not selected.
7. Select OK.
8. Rename the new query Automatic Email Matches.
9. Rename its existing Connection Name column to Xero Connection Name.
10. Expand the merged HubSpot column and select:
    • Connection Name
    • Company ID
    • Customer Key
11. Clear Use original column name as prefix and select OK.
12. Rename the expanded columns:
    • Connection Name to HubSpot Connection Name
    • Company ID to HubSpot Company ID
    • Contact ID to Xero Contact ID
    • Contact Name to Xero Contact Name

Add the HubSpot company name for validation

1. With Automatic Email Matches selected, open Home > Merge Queries.
2. Use hubspot companies as the second table.
3. Select Customer Key in both tables.
4. Choose Left outer as the join kind and select OK.
5. Expand the merged column and select only Company Name.
6. Clear Use original column name as prefix and select OK.
7. Rename Company Name to HubSpot Company Name.
8. Add a custom column named Match Method containing:

"Exact email"

Review the email matches

Compare Xero Contact Name with HubSpot Company Name. An exact email match is a strong signal, but it should still be reviewed when a generic address, external accountant or shared mailbox could be associated with more than one organisation.

Once the matches have been reviewed, remove Email Address and Normalised Email from Automatic Email Matches. They are no longer required by the mapping output and may contain personal data.

Set the final data types as follows:

Do not append the email and name matches yet. In the next step, we will give email matches priority, identify the remaining unmatched records and add a small manual overrides table for genuine exceptions.