Dreaded Data Model and Power Query loading error

[cmajka]

Good Morning -

I am sure this has been discussed on this forum ad nauseum, but I can't seem to find anything on it. I created several Power Queries and added them to the data model. Then I realized I needed to change one of the filters in one of the queries. I thought it should be easy enough. When I went to close & load to the data model, I got a dreaded error

"The query 'ABCD' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query."

One option was to create a copy of the query and that loaded just fine, however, since the name of the new query is 'ABCD (2)' I will still have to redo all of my connections and pivots.

Please tell me there is a work around...I just spent the last few days building this report with many pivots . I have excel 2013 and 2016.

Thanks!

 

[rae2blue]

Hi cmajka,

I was having the same issue when I came across your post. I managed to find the solution to the problem while trying to still research the answer and thought I'd share in case you still need a work-around or someone else has a similar issue.

[Steps for 2013 version]
1. In the Power Query menu, Workbook Queries, hover over the query with the issue until the pop-up preview window appears.
2. Select the three radial dots at the bottom of this window and select Load To...
3. Check or un-check Add this data to the Data Model.
4. Select Load.
5. Select Continue on pop-up warning window. Query will automatically refresh at this point.
6. Repeat steps 1-5 to reset to preferred setting (optional).

James Serra explains the purpose of Load To Data Model well. In 2013 Excel, the Load To Worksheet is actually captured by Load To Table v. Only Create Connection. You can have it still load to the data model with either preferred view.