I had built a power query based on the answer provided in this thread:
https://social.technet.microsoft.com/Forums/office/en-US/b1fe131a-ac7d-4681-80e1-074408986947/pivot-table-with-actual-values-instead-of-aggregates?forum=excel
The query worked great and I set up the properties to refresh evey 60 min as well as on file launch. User can always refresh manually as well.
I gave the excel sheet to someone to test and they are getting an error when clicking the refresh button:
"Initialization of the data source failed
Check the database server or contact your database administrator. Make the sure the external database is available and then try operation again. if you see this message again, create a new data source to connect to the database."
I dont understand, becuase all the data exists in that one excel file. When user selects the table 1 and table 1_2 that are refrenced in the query, it selects the correct source data set.
I can open the same file and refresh successfully.
Any ideas?