Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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...
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.
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.
"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:
"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.
"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.
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?
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.
@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...
Proud to be a Super User!
Paul on Linkedin.
is it a power bi question or power pivot question?
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |