Power Query / Pivot Tables doubles values

DMfba

New Member
Joined
Mar 21, 2019
Messages
46
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
Hi David,
Thank you very much. It was helpful.

Just adding some screenshots:
1- Expand
1690828387183.png


2- filter out the DefinedName
1690828425394.png
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,322
Messages
6,171,449
Members
452,404
Latest member
vivek562

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top