PowerPivot relationship based on two columns per table

Question

I have a table geoLocations which holds among others the two columns latitude and longitude. There is a second table (let's name it cities), which holds for each unique pair of laitude and longitude the corresponding city.

How can I model this relationship using PowerPivot? Creating two separate relationships will fail, as one coordinate may occur several times in the lookup-table (only the combination of both is unique).

Thanks in advance :)

Answer 1

You should create a key in each table that concatenates the 2 fields together. This could be done in a calculate column eg:

= LATITUDE & "-" & LONGITUDE

Or on import which would yield better performance on a large data set.

Jacob