change primary key power bi

[SophieBoucher]

Hello,

Anyone can tell me how we can change a colomn of Primary key in Power BI,

I have a table with 2 Colomn, i like to add an Index colomn and specify it to primary can, i can't do it.

Thank you 

[MarinaK]

I've run into a similar problem. On a table from Azure SQL having a proper pirmary key.
Following (some of the) steps above did help. Initially... Unfortunately the problem reappeared when I changed the filters in Power Query. Even with a Table.ReplaceKeys. So I started removing the columns being wrongfully assumed to be PKs again. And with every column removed, another faulty column (further along in the table) was chosen as PK.

From that I concluded Power BI has a bit of a mind of it's own 😉 and needed to be tricked with it's onw logic.

So I tried reordering columns. First started with all the columns that relate to the dimension tables. And after that the first column was my 'real' PK. This did the trick for me. Even with all the other columns back in place. And it's actually quite a quick change to make.
Hopefully someone having the same issue will find this post and is also able to fix this annoying issue quite easily.

[Amrjot]

Power BI seems to be saving the column/field name, as THE primary key, on which joins are made and a model created successfully.

Any changes made to the source thereafter still seems to use the same column as the primary key. If there are duplicates that violate and prevents creating the model, it will not apply any changes to the data source. It seems to be like a deadlock 😞

I have run into this issue and even tried suggestion given by @odanovich but it did not work.

Following is what worked and resolved this issue:

1. Filter the query with the exact same value which was causing the problem
   • This helps reduce the size of the model
2. Create a join to the other sources, using any other column. In your case any other other column than "Campaign"
   • It could be any column it does not matter
3. Try compiling the by Close & Apply
   • This will again fail
4. Now go back again and delete  or remove the column for which Power BI was reporting the error in your case it would be "Campaign"
5. Compile the model by Close & Apply
6. This model will compile successfully
   • Your visual might display error due to missing column you just delete but do not worry
7. Go back to Transform data and add the column again (in your case it would be "Campaign")
8. Compile the model again

The issue should be resolved now.

[Smizmar]

In Query Editor:

1. Do your Unpivoting work.

2. Add Column; Index Column; From 0 or 1.  Call it Index.

3. Create a copy of the column that the editor thinks is your primary key.  Call it "Primary - Copy".

4. Delete the column that the editor thinks is your primary key (so delete "Primary" column in this example.) This will force the editor to assume a different primary key.  It will choose the Index column as your new primary key.

5. Apply your changes.

6. Go back and rename the "Primary - Copy" to its orignial name.

[tharris]

I was having the same issue and this worked! Thank you!

[CarlosCuenca]

It works but instead of duplicate the column I deleted it, created the index column, refresh the model and them erase the step of deleting.  All in the Query Editor.

[WIshnowsky]

I had this same issue, and already had an index column. Deleting, applying changes then removing the delete step and applying changes worked for me as well. 

[v-yuezhe-msft]

Hi @SophieBoucher,

Could you please post the full error message in English and share sample data of your scenario?

Generally, we will need to create/define Primary key at data source level and then import it into Power BI.  Another option is to use DAX in Power BI to combine several columns so that each row has unique values.


Thanks,
Lydia Zhang

[MattAllington]

Why do you want to do this?  A primary key in Power Pivot is used to join a lookup table to a fact table.  There is no point doing this unless the key exists in both tables.  So it is not as simple as just adding a key.  The key must match, so an Index column alone wont help you.

[juan_pablo]

Primary Keys have more uses than for relationships, for exmple to avoid circular dependencies when using CALCULATE in a calculated column.

[SophieBoucher]

I want to change the primary key column to another column, because i applied an unpivot on my table and primary key has became not primary with duplicated values, and i need a colomn with identified values like Index, is it clearer?

[SophieBoucher]

That is the problem who appears

[odanovich]

I'm receiving this same error... I made edits to my query and now I cant change the data model to resolve it because the data model is waiting for the query changes to be applied... but those cant be changed due to the error that the column is considered the Primary Key of the table....

[odanovich]

To get around this issue, I added a Step in my Query to remove duplicates on the particular column... this allowed me to successfully apply the query.  Then I loaded the data model, deleted the relationship.  Next I went back in and removed my Query Step: Removed Duplicate to bring back to the original table and now I am reapplying the proper relationship.   Phew!  Problem solved. 🙂

[ImaDAXman]

It is likely because of the cardinality of your table relationship.  Try changing either from Many:One to One:Many (or vice versa).  

Also, make sure the cross filter direction is set to single instead of both.  The direction should be Dim > Fact.