none
remove Power Query queries while keeping the query output tables RRS feed

  • 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 at 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?

    Tuesday, February 17, 2015 12:02 PM

Answers

All replies

  • Duplicate the worksheet, right-click in the copied table and select "unlink from data source"



    Tuesday, February 17, 2015 4:17 PM
  • In addition to the suggestion above, you can also open Document Inspector and clean the custom XML data. After saving the workbook the queries will be removed.

    Tuesday, February 17, 2015 5:54 PM
  • thx, for this suggestion. I should have mentioned that the tables are used by charts as data source. Hence copying the worksheet is in my particular case unfortunately not a viable option. 
    Friday, February 20, 2015 8:47 AM
  • Thx Gil. This pointed me in the right direction. Actually your solution works without any worksheet duplication. 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.

    ps: I used the macro recorder in order to do step 2. with a VBA Macro instead of manual clicking. For some reason the macro code however does not clean the PQ XML. any suggestions? but i will make this a separate question anyway

    Friday, February 20, 2015 8:52 AM
  • You can unlink the existing tables and remove custom XML data in Document Inspector. No need to duplicate the worksheet.
    Friday, February 20, 2015 8:53 AM
  • You can unlink the existing tables and remove custom XML data in Document Inspector. No need to duplicate the worksheet.

    exactly. and even unlinking the tables is not neccessary for DocInspector to remove the custom XML of PQ.

    Do you have any suggestion on how to trigger the cleaning of PQ XML code via VBA? 

    the following code does not work:

    ActiveWorkbook.RemoveDocumentInformation (xlRDIAll)


    Friday, February 20, 2015 9:09 AM
  • Unlink will ensure the users won't get refresh errors from the connections that PQ generated.

    I found this link to Document Inspector Object Model. Never tried it :)

    Hope it helps.

    Friday, February 20, 2015 9:24 AM
  • Unlink will ensure the users won't get refresh errors from the connections that PQ generated.

    I found this link to Document Inspector Object Model. Never tried it :)

    Hope it helps.

    thx. will try someone to take it from there.
    Friday, February 20, 2015 9:44 AM