Relating a column (in one excel) which has a relating code, to the codes definition another table

[mhkarjiker]

Hi

I have two excel sheets I have imported into Power Desktop. The one excel has a revenue column with each row associated to a country code ($120; CA). In the other excel file I have the contry code (CA) in a column with its associated description (Canada). How do I link the two in Power? I basically want Power to see $120 is associated with country code CA and then associate CA to Canada

[Christann]

In the Relationships view,

drag and drop one of the country code fields onto the other one to create a relationship.

[mhkarjiker]

Thanks

When I tried doing that, it said:

" You can't create a relationship between these two columns because one of the columns must have unique values."

Help?

[Christann]

From what it sounds like you may have duplicate entries in you country table, say for instance two "Mexico's".

Try editing your query to remove duplicates.

First on your Home tab hit Edit Queries.

Then select a column in the countries table.

Next on the Query Editor Home tab under Remove Rows choose Remove Duplicates.

Finally hit Close and Apply.

Hope that helps!

[mhkarjiker]

In my one excel I do have duplicates for countrycode as multiple purchases were made from that country during a period, would I still then remove duplicates? would this not affect the revenue output?

[Christann]

If I understand you correctly your Revenue table should have a row for every purchase, but you only want each country listed once in the Country table because it is a reference table. So removing duplicates on the Country table should not affect the Revenue table.

[mhkarjiker]

Revenue table has a row for every purchase and a country code associated with it (duplicates because of multiple purchases), the second excel has country code with its country name in full. I want Power to associate the revenue with the country name...does this make sense? right now it is giving me the total from all countries 

[mhkarjiker]

Apologies if my question and explaination are unclear, however I found a youtube video which helped me. See below:

https://www.youtube.com/watch?v=vAvQ8pCnWDk