Issue on Power Pivot/Query Automatically Merge Duplicate Data

[tksnota]

Hi,

Is there a way to address the above issue because the sum of the pivot table and the original sheet is always different?

//NT

 

[tksnota]

Apologies I don’t know about the rule on cross posting.

Being new I will keep a note of it from now on.

 

[RoryA]

As far as I can see, the data you uploaded has not been changed since 13th Feb. Are you sure you uploaded the new data to the same location?

 

[tksnota]

@RoryA - the folder was created in Feb 13 but when you click it then new files will be shown (see screenshot).

I'm getting desperate on how to fix the issue why the amount in dc is showing £36k+ and not the -£199k based from the source excel namely the Data sheet filtered using project 2023-200 because for the past days i can't figure it out.

 

[tksnota]

btw...i'm posting my same query with the other forum so that i will not be violating any protocol. i really apologise for this action because i'm not aware. hope i will still get assistance from the forum members especially from @RoryA

link - SUM function not capturing All Amount

 

[RoryA]

Your relationship makes no sense to me. You have linked Amount DC in the Data table to Index in WIP?

 

[tksnota]

what’s the best relationship because the Amount_DC have duplicate values so I need unique values to connect it to the WIP?

very open to learn

 

[RoryA]

What field is common to both tables? It needs to be unique in one of them.

 

[tksnota]

the common one is project but they contain duplicates so this is the reason I used the index

 

[RoryA]

You can't just pick a join column simply because it is unique. It has to make sense. You may need to create a table of unique projects and join both tables to that.

 

[tksnota]

This is really getting interesting and I’m learning a lot!!! A few questions

1. Most of the projects that are common to both worksheets have duplicates. How can I use them to create unique values
2. Can I extract information from columns taken out from the Data Sheet and add index numbers then use them to establish connections
3. Any other specific suggestions will be welcomed

Thanks