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?
Hi Paulo,
It seems odd that the user would even go near the query, let alone change the name of it. The query name is also the table name in Power Pivot. Changing the query name results in a different table name, so the PivotTables built on the original table name can no longer find the data.
Honestly, I'd educate your users to stop messing with the queries as there's really no need for it.
That said, are you doing any transformations to the data in Power Query? If not, you can skip that step and import the data direct to Power Pivot via the Power Pivot window > Home tab > From Other Sources dialog. That way there's no query to change the name of!
Mynda
