Getting a listing of all countries from two different columns

[gemcityzach]

Hey folks,

I have a reference table that has two country columns (Country_To and Country_From). Each record has a unique ID and the record is about where a service is coming from or being provided. I want to get some sort of composite column that has all of the unique IDs and each country from both columns into one (not concatenated). The reason is that I want to add a second field using ISO Region (e.g., Asia, Oceania, Europe, etc). See second table.

I'm not even sure what the correct way to think about this problem is. I want to be able to show the number of IDs, which represent a service contract as the count factor by the Regions where the service is coming from or going to. Maybe this is dumb and I should handle it as Service To Exposure and Service From Exposure?

E.g., Total Service Exposure to Asia is 5,000 relationships

E.g., Total Service Exposure to Europe is 2,500 relationships

ID	Country_From	Country_To
123	United Status	Canada
234	Argentina	United States
234	Argentina	Canada
234	Argentina	Hong Kong S.A.R.
345	Canada	England
345	Canada	Ireland
345	Canada	France

Enriched with ISO Region

ID	Country	Region
345	Canada	North America
345	England	Europe
345	Ireland	Europe
345	France	Europe
234	Argentina	South America
234	Hong Kong S.A.R.	Asia
234	United States	North America

[ray_aramburo · Accepted solution]

I prepared a file with what I understood you wanted to achieve. Give it a check (look into Power Query as well) and let me know if it works for you: