Pivot Table linked to External Connection (Power Query)

[LucaA]

Hi All,
i am using as source of my pivot tables an external connection to a PQ query.
After having created the pivots i have changed the initial query fields (column categories etc) several times and now in my pivot in the excel i still see all the old categories even if they are not existing anymore in the query and when i filter them in the pivot they are blank as no more data related to them are present in the query.
Is there a way to refresh the pivot in order to keep only new fields? i dont want to cancel the old pivot and redo it (in this way the problem is solved as the new query does not include old fields
thanks
Luca

[v-juanli-msft]

Hi @LucaA 

As tested, if you connect to an external data source with excel and convert it to a pivot table, updates from the data source side can be applied into excel with "refresh" button clicked.

However, if you create a pivot table in one excel, then connect to tjis pivot table in another excel file, then you filter or delete some columns in the original pivot table, it can't be update to the other excel file.

Best Regards

Maggie

[LucaA]

@v-juanli-msft 

Hi,

thanks for the answer.

PQ query is in the file so as the PIvotTable related to it.

When i enter in the pivot filter i still see the old categories available even if they are no more present in the query. For example column "Product" which had values A,B;C originally but now it has only values A,B in the Query when uploaded in Excel, if i enter oin the PivotTable filter i still see product C

Any solution

thanks

Luca

[watkinnc]

Hey there. Is this a Power Pivot model, where you could refresh the model? If not, have you gone to the options and cleared the cache?

[LucaA]

Hi,

thanks for the answer.

No it is not a PP model, it it just a query of PQ imported only as PivotTable. I tried to clear the cache from PQ Options but nothing has changed, the problem is still there.

When i enter in the pivot filter i still see the old categories available even if they are no more present in the query.

Luca

[jpuste]

Hi Luca,

I may be wrong but, I think I had the same problem you are describing. I had some fields that the data model was remember even after the were no longer in my source query. I had to go to "Manage Data Model" under the data tab in Excel. Once that opened, I was looking at the tables of all the queries I had in the background of excel. I found the query data that was source source of my pivot table and found the columns that were still in my pivot table but should not be. I was then able to delete those columns.

I hope this helps.