Delete Power Query steps after loading to Power Pivot data model

Question

I need to delete query steps after loading the data into model. The reason is to hide the sources, protect our know-how, or maybe I'm just not very proud of what I've done ;).

But when I delete PQ connections or change "Load To" option, also the tables disappear from data model and pivot table becomes unresponsive. It's also not possible to modify or delete the connection created in Power Query from Power Pivot window, or even view table properties.

I could use Review > Protect Workbook > Protect Structure to disable viewing and editing queries / connections, but the steps are still visible, and the user cannot modify the workbook; even pivot table drill-through function doesn't work as it needs to create a new sheet to show data rows.

Answer 1

If you need to remove the query steps, then you have to store the data within the Excel file (since a query is just a set of instructions for how to connect to the data and transform it).

What you can do is create a query, load it to a table in an Excel sheet and then delete the query, leaving a static table. You can then create a pivot table using this static table as the source and it should function normally (though you obviously won't be able to refresh the data). I.e. don't create a data model until you've loaded your data and removed the query.