When you do this, on the very latest build of Power Query, you'll see a 'Possible Data Loss' warning dialog telling you that you'll lose any customisations you made. Do what the error message suggests and change the Load To option on the Power Query query, unchecking the option to load to the Data Model.The table in the Excel Data Model is unaffected, however, and your PivotTable continues to work - it's just that now you can't refresh the data any more. Close the query editor window and when the query refreshes, bang! you see the error above. Re-open the Power Query query editor, and then rename any of the columns in the table (not necessarily the one you changed in the previous step).The column name change will be reflected in the PivotTable and everything will continue to work. Go back to the PowerPivot window and rename one of the columns there.Go back to the worksheet and build a PivotTable from data in this table, using whatever measures or calculated columns you have created.Open the PowerPivot window in Excel, then create measures/calculated fields, calculated columns, relationships with other tables as usual.Import the data from a table in SQL Server using Power Query.Why does this problem occur? Let's take a simple repro. This is the single biggest source of frustration among the Power Query users I speak to a fix for it is being worked on, and I hope it gets released soon. There is a lot of helpful information out there on the web about this issue if you look around, though, and that's why I thought it would be useful to bring it all together into one blog post and also pass on some hints and tips about how to recover from this error if you get it. This post has a solution for the same problem in Excel 2010, but it doesn't work for Excel 2013 unfortunately. Please disable and re-enable loading to the Data Model for this query.

Here's the error message we got:ĬOM Error: The query 'xyz' or one of its inputs was modified in Power Query after this connection was added. We couldn't refresh the table 'xyz' from the connection 'Power Query - xyz'. Reposted from Chris Webb's blog with the author's permission.Īnyone who has tried to do any serious work with Power Pivot and Power Query will know about this problem: you use Power Query to load some tables into the Data Model in Excel 2013 you make some changes in the Power Pivot window you then go back to Power Query, make some changes there and you get the dreaded error

Power pivot for excel 2013