Question on Duplicates

[kattlees]

I have imported a table and can't create a relationship because it says there aren't unique values in one of the columns.

The problem is they are unique if you look at capitallization.

Example - Insurance Company table is the one that would list every insurance company with a unique code. (We are limited in our database to 3 characters and some have to start with certain letters)

Data may be:

Company                                 Code

Blue Cross                                BC

Blue Cross of SD                      Bc

First American                          FBx

Tricare                                      Tx

In another table (Accounts) we have 

Name                                      InsCode

Kathy                                       BC

Jim                                           BC

Sue                                          Bc

etc...

When I try and create a relationship between InsCode and Code it says neither have unique values. Is there a way to have it look at capitalization?

[pxg08680]

@kattlees

Where are you getting(importing) the data from. Try to get distinct Codes for both instead of all the codes.

[kattlees]

Here is a picture. On the right is in the query itself.  You can see the capitalization is unique. Thus giving unique values.

On the left is when I pull the values into a table. The capitalization changes.

[parry2k]

Power Query M is case sensitive but DAX is not.

So you need to add an index column in your master table in power query and use merge queries in your transaction table mathcing on code and get index number from master table, once this is done then you can set relationship between you master and transaction table on index column.

Let me know if you need help to get this up and running.