Pivot Table Data Source Not Found

[excel_novice89]

I have been handed a set of data that is run from a power query editor. The spreadsheets where pivots are contained within the file. There is a pivot table for which I am unable to find the data source. I have clicked on data connections, no luck, and I have clicked on pivot table analyze and queries and connections, no luck. I have searched the field heading to see if the header has been changed, the source is the same as the header, it almost looks like a pivot within a pivot, if that is a thing? Not sure where else to look, any ideas?

 

[Alex Blakenburg]

When you go to the DataSource does it look like this ?
ie Change Data Source greyed out and Connection Properties showing.
If so selecting Connection Properties should open queries & connections.
Switch to the Queries Tab and only the connected query should show.

How many queries do you have ?
If its not too many the long way is to look at the query properties (right click on the query) > Properties > Used in.

 

[excel_novice89]

Thanks for your message. I have that view but under change data source but Connection Properties is greyed out and Change Data Source is showing.

 

[Alex Blakenburg]

Then it's not based on either Power Query or the Data Model.
What is in the address box if you click on Change Data Source ?
Try copying whatever is in that box. Close the box.
Hit F5 (goto). Paste the value into the goto box and see where that takes you.

 

[excel_novice89]

That takes me to the large data set which was used however the field header in the pivot table isn't in that set of data I have unhid all data etc just in case I am missing something. It is quite weird.

 

[Alex Blakenburg]

I am not sure if you have already done this when you say you "have unhid all data".
But try this.

• Just refresh the pivot - to prove that it is still using that same data set
• Then go to one of the totals and double click on it.
  This will "drill down" / explode the data into a new sheet.
  Actually it may even be easier if you pick just one line.
• What I am expecting to see in the drill down, is all the columns you see in your data source.
  "With the same headings as your data source"
• If that is the case what I think has happened is that the Heading names have been manually changed in the Pivot Table.
  
  
  

If under the Pivot Fields Gear Icon you have the sort set to in Data Source Order and you don't have too many columns it shouldn't be to hard to compare them.​

​

If you comfortable with running code to the VBA immediate window I can give you some code that lists the Pivot Field names showing their Data Source names.