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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
digitalAstro
Frequent Visitor

PowerBi duplicating records

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?

3 REPLIES 3
Bahalzamon
Helper I
Helper I

@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
Super User
Super User

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

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

If we imagine table A:

fundIDfundNameprojectIDprojectNameStateamount
1fundA3projectXActive5
2fundB3projectXInactive2
3fundC4projectYActive10

 

table B (with the duplicate record):

projectIDprojectNamestartDateendDateState
3projectX01/01/2201/06/22Active
4projectY01/04/2201/04/23Active
4projectY01/04/2201/04/23Active

 

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:

digitalAstro_1-1670255764217.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors