Earlier this week I encountered a new Power Query error when I attempted to update a Power Query that I had previously created to load a Power Pivot model. Unfortunately, when queries are changed after a Power Pivot model is created, the underlying metadata does not automatically refresh. In fact, it’ll break the entire query until you fix it. Below is a sample of one way to fix it, however a word of caution before you proceed. This method involves removing the Power Query from the Power Pivot data model, which will unfortunately cause the loss of all of your Power Pivot development for this table. So if you spent a lot of time creating some calculated columns and measures on this table, pause for a moment and consider if you have any other options of implementing this fix. If you have another way of fixing this type of error, please post it the comments section of this post.
This fix has two steps. Remove the Power Query from the data model, and then add it to the data model again. To remove the query from the data model, open the Workbook Queries pane by selecting the Workbook icon from the Power Query tab in Microsoft Excel.
In the Workbook Queries pane, double click to open your malfunctioning query. In the bottom right corner of the Power Query window, deselect the checkbox Load to Data Model from the Load Settings menu. Then click the Apply and Close button at the top left of the Power Query window.
To add the Query back to the data model, open the query again, and check the Load to Data Model option in the Load Settings.
This is a simple solution, however the drawbacks are significant. Hopefully this issue can get fixed in the near future, or one of my amazing readers will propose a more elegant solution.