All solutions General QuestionsPIVOT TABLE Expert Solution – General Questions on Pivot Table

Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hello, I tried to attach my spreadsheet bit it is too large. I built a power pivot table with a customized formula and everything looked really good but when I try and update the information for the new day the only thing that comes up is the error #NUM!. I am struggling understanding why the formula works for data that was in the spreadsheet when I made it but any data that I update it with gives me the error.
Solved by S. Y. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 13/12/2017 - 09:14
good day
Excelchat Expert 13/12/2017 - 09:14
how may i help you today
Excelchat Expert 13/12/2017 - 09:14
can you send in the file
Excelchat Expert 13/12/2017 - 09:15
hello?
User 13/12/2017 - 09:15
I tried, It saws it is to large.
Excelchat Expert 13/12/2017 - 09:15
ok can you send a screenshot?
Excelchat Expert 13/12/2017 - 09:17
hi
User 13/12/2017 - 09:17
Sorry, I am trying.
Excelchat Expert 13/12/2017 - 09:18
i think we can try to troubleshoot by discussing
Excelchat Expert 13/12/2017 - 09:18
so you have a pivot table
User 13/12/2017 - 09:18
Okay
Excelchat Expert 13/12/2017 - 09:18
where is the source data located?
User 13/12/2017 - 09:19
The file has 4 tables in the same spreadsheet as the Power Pivot.
Excelchat Expert 13/12/2017 - 09:19
ok
User 13/12/2017 - 09:19
One of the tables is a Power Query that is pulled from a very large spreadsheet.
Excelchat Expert 13/12/2017 - 09:20
ok
User 13/12/2017 - 09:20
That is why the life is so big
Excelchat Expert 13/12/2017 - 09:20
which table is giving you the error
Excelchat Expert 13/12/2017 - 09:20
the one with power query or the others
User 13/12/2017 - 09:20
The error is with the actual pivot table
Excelchat Expert 13/12/2017 - 09:21
if i get you right
Excelchat Expert 13/12/2017 - 09:21
you have some sort of dashboard
Excelchat Expert 13/12/2017 - 09:21
with 4 pivot tables on it
Excelchat Expert 13/12/2017 - 09:22
is that correct?
User 13/12/2017 - 09:23
No sorry, three of the tables are just tables that I have uploaded to the data model and the other one is the power query that I have also loaded into the data model.
Excelchat Expert 13/12/2017 - 09:23
ok
User 13/12/2017 - 09:23
There is only 1 pivot table built with information from all the different tables.
Excelchat Expert 13/12/2017 - 09:23
so all data was loaded into the data model
User 13/12/2017 - 09:24
Correct.
Excelchat Expert 13/12/2017 - 09:24
so where do you try to add new data?
User 13/12/2017 - 09:25
The new data comes from a data dump file that I access through the query I refresh every day.
Excelchat Expert 13/12/2017 - 09:25
i suggest you recheck your query
Excelchat Expert 13/12/2017 - 09:26
see the steps
Excelchat Expert 13/12/2017 - 09:26
also the query should start by preferably selecting data source as a folder
Excelchat Expert 13/12/2017 - 09:27
you can dump all the files you want the query to extract data from there
Excelchat Expert 13/12/2017 - 09:27
power query will be faster when all data is in one location
Excelchat Expert 13/12/2017 - 09:28
i see it's only tuesday that has a problem
Excelchat Expert 13/12/2017 - 09:29
are you there?
User 13/12/2017 - 09:29
Yes, but when I update it tomorrow it will be Tuesday a Wednesday.
Excelchat Expert 13/12/2017 - 09:29
but did it work from sun and monday ?
User 13/12/2017 - 09:29
But if I rebuild the spreadsheet and put in all the current information there will not be a problem until I updat it again.
Excelchat Expert 13/12/2017 - 09:29
when you used it on monday
Excelchat Expert 13/12/2017 - 09:30
i understand
Excelchat Expert 13/12/2017 - 09:30
the problem is with your query
User 13/12/2017 - 09:30
Yes I have a slicer to only show one week at a time
Excelchat Expert 13/12/2017 - 09:31
recheck your steps on the query you are using
Excelchat Expert 13/12/2017 - 09:31
also, is the name of your source file the same?
User 13/12/2017 - 09:32
The same name as this current file?
User 13/12/2017 - 09:32
No
Excelchat Expert 13/12/2017 - 09:32
i suggest you load your data onto a table instead to a data model
Excelchat Expert 13/12/2017 - 09:32
so that if there is a problem of this nature, you can troubleshoot it
Excelchat Expert 13/12/2017 - 09:33
create your pivot table from the data loaded from a query
User 13/12/2017 - 09:34
Okay, I will give that a try. Thank you for your time.
Excelchat Expert 13/12/2017 - 09:34
you are welcome
User 13/12/2017 - 09:34
Have a nice day.
Excelchat Expert 13/12/2017 - 09:34
you too :)

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc