CODEWITHSUNDEEP
exceloffice365powerquery
Nick van H.
Nick van H.

Reputation: 388

Excel query gets duplicated when adding pivot table

In Excel I have several queries, e.g. MyQuery. When I add a Pivot Table with this query as data source (Insert > Pivot Table > Use an external data source > Choose connction > MyQuery), the query definition gets duplicated to MyQuery (2) and the connection is made to the new query.

Removing the pivot table and the new query doesn't solve the problem. Reconnecting the pivot table to the old query also doesn't help, as it will remain connected to the new query.

Googling this problem didn't result in much luck, as any "query/duplicate" related searches result in how to handle duplicate rows/columns.

How do I solve my duplicate query definition problem?

Thanks in advance, Nick

Upvotes: 3

Views: 2423

Answers (3)

wiero
wiero

Reputation: 2246

[Office 365] open "Queries and Connections" from Data ribbon. Then right click on query and choose "Show the peek". Then at the bottom there should be more button "..." which will show "Load to" option.

After that "Import Data" window pops up and allows us to create table / pivot without connection duplication :)

Upvotes: 0

Charlie
Charlie

Reputation: 21

I have exactly the same issue. Each time I create a pivot table directly from a table in the data model it creates a duplicate table and adds it to the data model. The tables are unsynced which makes it unmanageable if you have a lot of pivot tables and pivot slicers also won't work because technically the tables all have different data sources.

The best workaround I have is to load the data model table into a worksheet and use that as the data source for the pivot tables.

Upvotes: 2

horseyride
horseyride

Reputation: 21318

The issue started immediately after updating to Office 365 ProPlus 1708.8431.2215 If you create a PowerQuery using connection only, when you go back to reference the connection in a new pivot table it shows as Query(1). The only work around I've found is to copy the code and then recreate the connection from scratch with a pivot report associated at the start of the process

Upvotes: 2

Related Questions