PowerBi duplicating records

[digitalAstro]

Hi all!

I have a problem and I simply cannot find the solution.I have a DirectQuery from Dataverse (Microsoft Dynamics), with a table showing funders and funding to different projects. From the same Dataverse, I have another table with project info (projectID, projectName, etc.). The same founder could be for many projects, and projects can have multiple funders.

My problem started when I created a bar chart, where x-axis is funder and y-axis is the sum of the amount. The problem: For one of the funders, the amount shows up double the amount in the table. We can say this is FunderA - in a bar chart, the funding shows as 10.

To understand what was wrong, I tried to see how many fundID it counted, and it said two. I cannot see two records in the table, but in the linked project table I can see a duplicate record for the project. So I am assuming that this is causing the issue. I don't know how this happened however - I have refreshed it plenty, and it looks correct in the Dataverse. Both records are exact duplicates (down to when they were created and last modified), but as it is a DirectQuery, I can't remove the duplicates (I need the data to be live). I saw you used to be able to enable all functions for DirectQuery, but it seems they removed this feature since then.

Is this something someone has experienced and knows a workaround for?

[Bahalzamon]

@digitalAstro did you ever identify the issue? I have teh exact same thing. 4 records 100% identical down to the GUID. I have done a quick fix of modifying the details to use distinct values, but cant multiply it across every tab unless i do remove duplicates in the model, which will make the model import rather than direct query. 😞

[lbendlin]

This would be more of a question for your data model. How does that look like between these two tables?

[digitalAstro]

The two tables are linked on the unique key project ID - many to one.

If we imagine table A:

fundID	fundName	projectID	projectName	State	amount
1	fundA	3	projectX	Active	5
2	fundB	3	projectX	Inactive	2
3	fundC	4	projectY	Active	10

table B (with the duplicate record):

projectID	projectName	startDate	endDate	State
3	projectX	01/01/22	01/06/22	Active
4	projectY	01/04/22	01/04/23	Active
4	projectY	01/04/22	01/04/23	Active

The duplicate record does not show in Microsoft Dynamics, so I can't delete it or change state and filter from there.

Edit: For some reason the formatting comes out strange, so here is a screenshot: