Power Query / Pivot Tables doubles values

[DMfba]

Hi Everyone,

I could use some of the expert advise here. I am in the middle of large project where I encountered either the Pivot table or Power query are doubling the values.

What I am doing.
I am using Power Query to link to a folder to bring in mulitple years of data and create one large table. Then using Pivot tables to summaries monthly and yearly values.

The Problem.
Recently, I cleaned up some of the blank sales names within the 2019 data. However, once i refreshed the data all of the 2019 values are being double. The raw data file shows the total sum value of $5MM, refreshing the Pivot table it now shows $10MM. The same holds true for monthly values. April shows $549k but pivot table now shows just over $1MM. This is only true for the 2019 files where the Sales Names were updated. The remaining years are correct.

Is there way to prevent this? Does anyone know what causes this to happen. What would cause the raw data file to be one amount but the pivot to double the value?

 

[jonni]

Did you get a solution on this scenario ?

 

[DMfba]

Hi Jonni,

I never got an answer but I did find out what was causing the problem. If you are have a similar issue, here is what was causing my problem. In the process of other users update the data they put filters on the headers. With over 60 files to review I missed the ones that had it. Power Query does see the filter as something separate. So in you query steps it will show up as Defined Name if I remember correctly. Which means Power Query is seeing that data twice. When you combine all of your files to a master table you will any data with "defined name" in there twice and it will be overstated.

To avoid the double counting, filter for "Sheet" on the Kind field. This will only bring in the raw data and not anything other users worked on.

Hope this helps. Thanks

David

 

[bymofi]

Hi David,
Thank you very much. It was helpful.

Just adding some screenshots:
1- Expand

2- filter out the DefinedName

 

[jdellasala]

Just as a wild guess, it sounds like when Power Query uses the current workbook to pull in data, and then delivers a table to a new worksheet, and then includes that table in the refresh.
After the worksheets/tables are pulled in by Power Query, the list of tables/worksheets has to be filtered to not include the resulting table.
As I said, just a guess, but maybe it will give you a clue as to where to look for the problem. Good luck!