Forum Discussion
Power BI is creating duplicate entries
I have 2 data sources from Excel for my report connected with a Many to many relationship on one of the columns (Client Name). When I try to visulise the data in a table and I select fields from both my sources Power BI created duplicate entries with the total combination of whatever the duplicated sources are.
For exampled my first data source looks like this:
Client Start Date
Client 1 01/01/2022
Client 1 01/02/2022
Client 2 10/01/2022
My second data source looks like this:
Client Project Project Type
Client 1 Client 1 Project 1 Project Type A
Client 1 Client 1 Project 2 Project Type B
Client 2 Client 2 Project 1 Project Type A
However when I create a report and insert a table with data fields from both sources it looks like the following:
Client Start Date Project Project Type
Client 1 01/01/2022 Client 1 Project 1 Project Type A
Client 1 01/01/2022 Client 1 Project 1 Project Type B
Client 1 01/02/2022 Client 1 Project 2 Project Type A
Client 1 01/02/2022 Client 1 Project 2 Project Type B
Client 2 10/01/2022 Client 2 Project 1 Project Type A
Does anyone know why this is happening and how to stop it?
Hi Anonymous ,
This is because you have multiple [start date] for a single client in Table A. When you put the [start date] to Table B, Power BI cannot determine which date this client corresponds to. In this case you will need to add an index column for both tables and use the index column as a one to one relationship instead.
Best Regards,
Jay
5 Replies
Hi Anonymous ,
This is because you have multiple [start date] for a single client in Table A. When you put the [start date] to Table B, Power BI cannot determine which date this client corresponds to. In this case you will need to add an index column for both tables and use the index column as a one to one relationship instead.
Best Regards,
Jay
Hi Anonymous ,
If the order of the data in the two tables is one-to-one, you can directly add the index column in the Power BI Editor.
If not, you could create a rank column in both tables based on the date and project columns for each Client. Then use this rank column as relationship.
rank = TableA[client]&"_"&RANKX(FILTER('TableA','TableA'[client]=EARLIER('TableA'[client])),'TableA'[date],,ASC) rank = TableB[client]&"_"&RANKX(FILTER('TableB','TableB'[client]=EARLIER('TableB'[client])),'TableB'[project],,ASC)Best Regards,
Jay
- mh2587
Super User
Change the relatioship to one-to-many
then check it
