Excel and PowerQuery - Data Model RRS feed

  • Question

  • I use Excel 365, but the problem also happens in Excel 2016.
    I use Power Query in Excel to make a remote connection to another Excel file.
    The data in the source file is in Table format.
    When the connection is finished, I return the data as Just Create Connection and check the option Add to Data Model, since I see no sense in returning as a Table, as the data will be analyzed in several Pivot Tables.
    Adding to the Data Model assures me that when creating multiple PivotTables, the Power Query query will not be duplicated.
    It turns out that if a user changes the name of the query, the layout of the PivotTables is lost.
    If I do not check the option Add to Data Model, the user can freely change the name of the query that will not affect the PivotTables.
    But when creating the PivotTables, at each PivotTable the query is duplicated.
    Is there a solution to the problem?

    Instrutor de informática em gráficos avançados e dinâmicos, usando fórmulas e formulários

    Sunday, January 26, 2020 11:52 AM


  • Hi Paulo,

    I think there are two distinct issues here. 

    I agree with your desire to import the data once, and land it directly in the Data Model.  

    Issue #1: (this is a valid complaint about Power Query): Renaming a query causes the underlying table to be deleted and then a new table is created.  This means that all measures in the table will be lost.  Even if you don’t author any DAX measures, any implicit measures created by dragging fields into the Values area will be lost on the first refresh of the PivotTable.  You can easily recreate the implicit measures, but when you have multiple PivotTables this is really annoying.  I think the Power Query team should consider doing the work to recreate the measures as part of the query rename operation, (or refrain from deleting the table,) when a rename operation is performed.

    Issue #2: (likely a mistake in usage): When you create PivotTables, please make sure you are creating a PivotTable based on the Data Model and not a PivotTable based on the external data source.  I don’t know precisely what entry point you are using, but you should not be seeing new connections or queries being created for each PivotTable.  Please make sure the data model is the source for each PivotTable.  One way to accomplish this is to use the option shown below in the dialog that comes up after Insert --> PivotTable, as shown here:

    I believe that if you do this, you won't see separate connections/queries being created.   

    Hope this helps,
    -Howie Dickerman
    Program Manager, Excel product team

    Thursday, February 13, 2020 11:37 PM