Remove Power Query queries while keeping the query output Tables

Question

I use Power Query to load data from external sources into several Excel Tables. Before sending this Excel to a client I would like to remove all Power Query queries (M code) while keeping the output/query Tables in place. My current workaround is:

1. unload Power Query
2. convert each table to range
3. load Power Query
4. delete queries (M code)

Is there a better/faster way to achieve what I want?

Answer 1

In addition to the workflow that xChillOut's answer describes, if you don't want to leave trace of power query use then delete the connection string from Data->Connections as well, otherwise Excel may keep on showing the Enable data connections warning message while opened at other end.

Answer 2

I've run into the same issue xChillout, this approach may be marginally faster:

Create a copy of the worksheet containing the data (via Right Click worksheet tab > Move or Copy > Create a copy OR Copy & Paste of values+formatting onto a new sheet)

Now when you delete the Workbook Query from the Power Query Navigator Pane, your data table is still available on the new sheet.

Answer 3

Thx to comments on an other forum I have found a better way. Here's my current (and much quicker) workflow:

1. disable Power Query addin
2. run Document Inspector and clean XML data

After that the PQ queries are gone and the tables are still there and untouched