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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Naomig2088
Helper II
Helper II

Refresh error duplicate values in the table

Hi,

I'm experiencing a refresh error that states that there are duplicate values in a column in the data table.

"Column 'StartofMonthLeaveDate' in Table 'Leavers for Turnover (2)' contains a duplicate value '01/10/2020 00:00:00' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."

 

It's correct that the duplicates exist as I have grouping based on two factors (date and department), so there has to be duplicates (screenshot of table below).

 

Can anyone suggest a way around this please?

Many thanks,

Naomi

 

Screenshot 2024-05-08 153423.png

6 REPLIES 6
Naomig2088
Helper II
Helper II

Thank you for your reply. Would adding an index column as the primary key column work?

Only if the index in both tables corresponds to the record with the same department / date combination. You'd be better off creating a new key that concatenates the  department/date.


An alternative if you have access to edit the tables in PowerQuery and if you don't actually need your leavers data in a seperate table would be to merge the two tables in PowerQuery prior to loading into PowerBI since unlike PowerBI, PowerQuery does support joining tables using composite keys.

Forgive me, I'm fairly new to Power BI. How would I go about creating a new key that concatenates the  department/date please?

In PowerBI, just create a new calculated column in each table, e.g.

 

UniqueID = 'Leavers for Turnover'[StartofMonthLeaveDate] &  'Leavers for Turnover'[Positions V2 (2). DepartmentNumber]


You'll need to swap out the table name 'Leavers for Turnover' for the relevant table name when adding it to the other table.

 

Alternatively you could create the new key in PowerQuery using merge columns see https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9... 

 

Then once you have your new key in each table you'll need to remap the two tables using the new key in the schema view.

 

Hi,

Thank you. I've managed to resolve the error so that the refresh is working but I think it's my grouping that's causing issues...

As the table is grouped using StartMonthLeaveDate and Department it's not producing the correct results are there are duplicates for StartMonthLeaveDate. Is there a way to group on StartMonthLeaveDate while retaining Department information please? This is what my grouping looks like currently:

Screenshot 2024-05-09 104634.png

halfglassdarkly
Responsive Resident
Responsive Resident

You either need to change the cardinality of the table relationship from many to one to many to many (but many to many relationship isn't usually a good idea) or create a new key in both tables that concatenates your date and department values and remap the relationship between the tables using the new key.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors