Pivot Table Data Source Not Found

excel_novice89

New Member
Joined
May 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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.

1621848005150.png


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.

1621847734563.png
 

excel_novice89

New Member
Joined
May 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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.

    1621906158352.png
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.​
1621906327715.png

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.
 

Forum statistics

Threads
1,141,770
Messages
5,708,432
Members
421,568
Latest member
Huxley

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top