locked
Pivot Table from Power Query RRS feed

  • Question

  • This didn't use to occur, but now when i create a pivot table directly from a power query "connection", it automatically creates a second power query, which is a copy of the first, and that is the one that feeds the pivot table. This seems like a major issue. Because now when i go back to my original query, changes there do not populate to the duplicated query and thus not to the pivot table either... 

    Does anyone know what is going on with this? And how to best deal with?

    Tuesday, December 19, 2017 6:45 PM

Answers

  • Hi,

    This is actually by design. The model we strive to is to have 1-to-1 relationship between the Power Query query and the underlying Excel connection. Conceptually they are two parts of the same ETL entity – Power Query query is responsible for extracting and transforming data, while the underlying Excel connection is responsible for loading the data.

    We implemented this model in Excel 2016 (e.g., when creating a new PivotTable via Data > Existing Connections), but apparently a few holes left. This was one of them.

    Hope it helps.

    Guy

    - Excel Team


    Guy Hunkin - Excel Team

    Tuesday, January 9, 2018 10:49 AM

All replies

  • I've experienced this behaviour for quite a while. To solve this, load the data to the data model (Power Pivot) and take that connection as source for your pivot table. That will prevent duplicating the query connection and updates to your query will be reflected in the pivot table.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, December 21, 2017 5:25 AM
  • Hi,

    This is actually by design. The model we strive to is to have 1-to-1 relationship between the Power Query query and the underlying Excel connection. Conceptually they are two parts of the same ETL entity – Power Query query is responsible for extracting and transforming data, while the underlying Excel connection is responsible for loading the data.

    We implemented this model in Excel 2016 (e.g., when creating a new PivotTable via Data > Existing Connections), but apparently a few holes left. This was one of them.

    Hope it helps.

    Guy

    - Excel Team


    Guy Hunkin - Excel Team

    Tuesday, January 9, 2018 10:49 AM
  • But there is one important issue...

    If you load your connection's data to Data Model it makes problem for all users that don't have PowerPivot Add-Inn, they can't e.g. filter data in Pivot Table, pivot columns, they just can see the data, nothing more. So for me it's not a solution in this case.


    Greg

    Friday, February 9, 2018 11:51 AM
  • But there is one important issue...

    If you load your connection's data to Data Model it makes problem for all users that don't have PowerPivot Add-Inn, they can't e.g. filter data in Pivot Table, pivot columns, they just can see the data, nothing more. So for me it's not a solution in this case.


    Greg

    I agree with Greg.  PowerPivot doesn't exist for Mac and also doesn't work well on protected documents and is therefore not a good intermediary for all cases.  

    Thursday, March 15, 2018 10:57 PM