Forum Discussion
Helper IIRefresh 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
6 Replies
halfglassdarklyResponsive 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.
Naomig2088Thank you for your reply. Would adding an index column as the primary key column work?
- halfglassdarkly
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.- Naomig2088
Forgive me, I'm fairly new to Power BI. How would I go about creating a new key that concatenates the department/date please?