Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

13 REPLIES 13

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,

ScottStauffer_0-1596850866410.png

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...

v-jayw-msft
Community Support
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

A bit late to this thread, but are you serious? This seems like such a basic feature. If there is a work around, I'll have to do that for all tables in my report due to those tables being SQL Views.

Anonymous
Not applicable

"AFAIK, there's no such feature in Power BI"

And how on Earth this tool got at the top of Gartner?..

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

"I'm not sure why you want to set an exist column as primary key"

Because I want to configure the column - and hence set the rules around data in this table - with the rules that define any PK: no null values, no duplicate values. Because otherwise I wouldn't be able to implement certain configuration involving this column - PBI won't allow it if the data is not unique and/or contains nulls.

Anonymous
Not applicable

So My guess is you want to create a unique key for each Row so that you can filter and manipulate the data. Is this what you are trying to do? 

can you share an example of what you mean by implementing certain configuration for the data?

Anonymous
Not applicable

The goal is to make the model consistent with the nature of the data loaded into a particular table. If specific column is the primary key in the source data, it should be as well in the model - same logic enforced

Hi @Anonymous ,

 

Understand. Let's say you have a column named id with values (1,2,3) and you want a feature that can set the column as primary key. Once you use like replace value feature to replace "1" to "2", it will pop-up error message and forbid your operation.

As far as I know, there is no such feature currently and you need to submit your idea through the link above.

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
PaulDBrown
Community Champion
Community 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

2020-02-29 (2).png

 

3) use this new table Values with "Primary Key" in all your expressions in measures, visuals, filters, slicers...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User

is it a power bi question or power pivot question?

Anonymous
Not applicable

 It is about Power BI Desktop, as forum name suggests.

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]

 

Refer: https://docs.microsoft.com/en-us/power-bi/guidance/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.