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.