2

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?

Improve this question

4 Answers 4

Reset to default
5

Using Excel from Microsoft 365 (Version 2112 Build 16.0.14729.20254) 64-bit:

The following lists more steps than in the original question, but these are really just basic menu/ribbon clicks... very direct and simpler than other answers.

  1. Click anywhere in the linked Excel worksheet table

  2. Click on the "Table Design" ribbon

  3. Click "Unlink" in the External Table Data command section enter image description here

  4. Click the Data ribbon

  5. Click Queries & Connections to display the side panel

  6. Locate the query which should now indicate "Connection only."

  7. Delete the query (Del key or context menu Delete)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

do you know how to re-establish the link once the data has been unlinked? I clicked "unlink" but now want to re-link the data but the "Edit Links" button is now greyed out...
2

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

Improve this answer

Comments

2

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.

Improve this answer

2 Comments

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
I understand , I wanted to leave a comment on the XchillOut post but I was not allowed may be because I am new to forum.
0

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.

Improve this answer

2 Comments

Thx Tom. unfortunately this worksheet duplication is not a viable option as the tables serve data to charts.
In my case, the "Move or Copy..." command just duplicated the query and connection along with the data, so it did not completely free up the worksheet from the data connection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.