For me, I think the error came about because:
1. I had changed a query name.
2. That query fed a Pivot table a table or something which was on a tab I then deleted.
Something like that anyway. I went all around the house trying to figure it out.
In the end the fix was to right click on the offending query and select "Load to". you'll get this box
For me the error came about partly because I didn't want this connection to appear as a Table in the first place, (but I'm still figuring out how best to keep manage which tables you want to appear in the front end of Excel and which you don't. PowerBI has a handy feature for this but Excel is a bit of a nightmare. At least at the moment it does).
Anyway, as a result, I found that if I unticked "Add this data to the Data Model" when I had already selected "Only Create Connection" and OK'd it, then when I went back into this and re-ticked it, the error went away.
I'd advise caution because if your queries are tied together in a complicated configuration then it might cause knock-on issues. So particularly if you're taking this spreadsheet on from someone else check thoroughly that it's not going to mess anything else up.
And if you're with Microsoft & reading this, then please can we have the same chance to select which queries do and do not appear in the front end of Excel after we're done with them in Power Query.
