Pivot Table Data Source

[wildturkey]

Hi

I have an external data source in excel which I need a pivot table on, the data is growing and Im looking for a way of automatically changing (Expanding) the data source.

The data comes from a web query and I am able to get the link from a right click on the data, but I cannot use this link as an external data source for the pivot table so it would appear I need a makro - I can find these to refresh data but not to expand the data selection.

Thanks

 

[sandy666]

Make source data as Table
From default workbook use PowerQuery to "import" data from source file
Create PivotTable with ExternalData (QueryTable)
---
if source data will be updated, refresh PivotTable and you should see new data in the PT.
it doesn't matter your source file will be open or closed

 

[wildturkey]

Data comes from a cloud based finance package and the link to the data source (Right click on data then edit query) is longer than 256 characters so rejected when trying to set a pivot table to run from an external source..I also get a stack of script errors when editing the query..

 

[sandy666]

External source for PivotTable means connection to the local QueryTable on your default workbook not to the cloud

eg.

 

[wildturkey]

Tried that, I get 'The type of connection selected cannot be used to create a pivot table'

 

[sandy666]

could you post what are you doing from the beginning? step by step?

 

[wildturkey]

Not sure myself The spreadsheet is created via our finance package and opens as a worksheet with an external data link - that link can only be amended using the finance package but changes made there filter through when the data is refreshed. As such no links are created directly in Excel and I don't think they can be, that's why I think I need to go down a makro route if possible?

 

[sandy666]

did you try : Data - New Query - From Other Sources - From Web then paste link, OK etc....?

 

[wildturkey]

More errors sadly

 

[sandy666]

other way...
Can you save this file (opened from the link) locally?