Power BI is creating duplicate entries

[Sauqiramo]

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?

[Anonymous · Accepted solution]

Hi @Sauqiramo ,

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

[Sauqiramo]

Hi Jay thanks for answering, I'm still pretty new to BI so apologies if this is an obvious question, but what data would I be putting in the index columns?

[Anonymous]

Hi @Sauqiramo ,

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]

Change the relatioship to one-to-many

then check it

[Sauqiramo]

Power BI wont let me chose one-to-many as there are multiple entries on both data sources.