I have 2 sheets in an excel workbook. Sheet 1 gets the data from another workbook using power query which gets refreshed when opening the excel file. Sheet 2 contains an pivot data of Sheet 1 and and a Pivot chart is prepared based on Pivot table. I have checked the option under Analyze >> Options >> Data >> Refresh when the excel file opening. But the Pivot table is not getting updated whenever the sheet 1 gets updated.
I also tried to add this Pivot chart to Data Model while creating the same and checked the property refresh the data when opening the file under connection properties. My Pivot data is not yet get updated using these 2 options. We are planning to share this excel file as read only and macro free to customers by uploading it in share point folder. Can you please help me to update my pivot table without using the macro please?