Forum Discussion
Set existing column as Primary key (index)
How do we set up existing column in a given table within the model as a primary key?
Note that we do not want/need to create an additional index column. We want to tell the model: this column is primary key, therefore applying usual PK restrictions to it - value cannot be null and there may not be rows with duplicate values.
Note that we know how to remove empty/duplicate rows when importing data into the model; this is NOT what we need to do.
14 Replies
- ScottStauffer
Advocate I
In Power BI Desktop, Go to the Model Tab on the Left,
Click on the Table in the Model View,
Look at the Properties pane on the right,
Key column... This is the primary key, is it not... The difference between a Primary Key and an Unique Key is...? Well, a Unique will allow null, but just 1 null row. This MAY ro MAY NOT be the case with Power BI...
- amitchandak
Super User
is it a power bi question or power pivot question?
- amitchandak
In power bi, I do not remember we use term index. Yes, we do create a table of distinct values(dimension) and join it back with the fact table. To make sure we are in a star schema model.
Table 4 = DISTINCT('Sales'[Brand])This is the table for the brand from sales and joins on brand with sales
I can add a numeric key to brand
Id = RANKX(all('Table 4'),'Table 4'[Brand],,ASC,Dense)I need to put back key to sales
//ID in sales table
brand id = minx(filter('Table 4','Table 4'[brand] = Sales[brand]),'Table 4'[ID])now I can join brand[Id] and Sales[Brand Id]
PaulDBrownCommunity Champion
Anonymous
1) create a new table (modeling tab in ribbon / New table) with distinct values for the column you wish as primary key:
Primary Key = CALCULATETABLE(VALUES(Table1[Column (PK)]); Table1; NOT(ISBLANK(Table1[Column (PK)])))2) join this new table to the orginal by joining the corresponding columns in a single-to-many relationship
3) use this new table Values with "Primary Key" in all your expressions in measures, visuals, filters, slicers...
Hi Anonymous ,
Interesting question. AFAIK, there's no such feature in Power BI.
As you said we can create index column or remove empty/duplicate rows in Power BI, I'm not sure where we can use primary key.
BTW, we can do few operations on rows in Power BI.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Please check this document.
https://docs.microsoft.com/en-us/power-bi/desktop-what-is-desktop.
Power BI Desktop is a free application you install on your local computer that lets you connect to, transform, and visualize your data.
The most common uses for Power BI Desktop are as follows:
- Connect to data
- Transform and clean that data, to create a data model
- Create visuals, such as charts or graphs, that provide visual representations of the data
- Create reports that are collections of visuals, on one or more report pages
- Share reports with others by using the Power BI service
"We want to tell the model: this column is primary key, therefore applying usual PK restrictions to it - value cannot be null and there may not be rows with duplicate values."
I'm not sure why you want to set an exist column as primary key since you can't insert rows in Power BI as you can do in database. Or you just want to delete empty/duplicate rows by one click?
There's no such feature that you can select a column then click the button to set the column as primary key. You can submit your idea through the below link.
https://ideas.powerbi.com/forums/265200-power-bi-ideas.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oh well. Better late than never, I guess...
In Power Query M, Table.AddKey "adds a key to table, where columns is the list of column names that define the key, and isPrimary specifies whether the key is primary".Signature: Table.AddKey(table as table, columns as list, isPrimary as logical) as table
Official documentation: https://learn.microsoft.com/en-us/powerquery-m/table-addkey
Right-click last step, select "insert step after",
then edit the new step code replacing `LastStep` (for example) by
Table.AddKey(LastStep, {"SomeColumn", "Maybe Other Column"}, true).
