Relationship between two tables with partial matching countries

[Anonymous]

Hi all,

I have two separate table a customer table and a sales table and i would like to display the country's sales and #of customers in the same table. 

However, the country list in each table is not 1:1 compatible.

Here are the two tables.

I would like to somehow join them together, and keep the sales table country list, then just leaving the customer raw lines empty if no values are found in the customer table.

I also have a calendar table that is currently combined between Customer & Calendar.... and Sales table & Calendar.. (displayed in picture further down)

Sales table (country list)	Customer Table
Australia	Australia
Austria	Austria
Belgium	Belgium
Brazil	Canada
Canada	China
China	Denmark
COSTCO	France
Czech	Germany
Denmark	Italy
Distributor Sales	Japan
Distributors – NJH	Korea
Distributors – NJH (	Netherlands
Finland	New Zealand
France	Norway
Germany	Singapore
India	Spain
Italy	Sweden
Japan	Switzerland
Japan NJH	UK
Kind KA	US-BT
Korea	US-Costco
Netherlands	US-Interton
New Zealand	US-Other
Norway	US-RS
Singapore	US-VA
Spain
Sweden
Switzerland
UK
US Audigy Group
US Audigy Retail
US BT
US Interton
US Operations RS
US RS
US RS Corporate
VA

Essentially i want to display a table like this:

[v-piga-msft]

Hi @Anonymous ,

By my tests with your sample data, it seems that you could create the realtionship for the two tables with country column like below.

If you still need help, could you please share some sample data and your desired output so that we could help further on it?

Best  Regards,

Cherry

[Anonymous · Accepted solution]

I found a solution using a bridging table.

Here are the steps

1. Export all country tables to excel files

2. Append all countries in 1 column and remove duplicate values. (optional: you can also adjust the names and make a sort key)

3. Create a new table in Power BI and paste the values into the table.

4. Make connections between country and the newly created country table.