Two automations to share Power Query results without allowing to refresh the query - T0022

[@iankr]

Many thanks, Celia. This is wonderful, and you explain it very clearly. I need to do exactly this with a project at work, where I need to improve on an existing query that produces several very large tables that feed a multitude of SUMIFS formulas in reports on multiple sheets in the workbook. (Alas, the reports are too complex to use Pivot Tables, hence the SUMIFS.) I need to retain the original tables after unlinking them from the query, then refine the query to produce a new set of smaller tables (via Grouping in Power Query) that have slightly different names. After I am satisfied that the new set of tables is correct, it is then a simple case of repointing the SUMIFS formulas to the new set of tables (via careful use of Find & Replace) and then finally delete the old tables to produce a workbook with a much smaller file size. But you have to retain the old set of tables while you're doing this, otherwise the SUMIFS formulas will error out with #REF!s all over the place!

[@stephenkaras1523]

Great video solved a problem that was causing me much frustration. Please keep creating such great content

[@CeliaAlvesSolveExcel]

Thank you for your feedback, Stephen. It pleases me to know that you took advantage of the content.

[@paryonoono5019]

Your teaching is really clear and helps me so much. Gratitude

[@CeliaAlvesSolveExcel]

Paryono, grateful for your feedback. It encourages me to keep sharing what I learn. All the best!

[@paryonoono5019]

@@CeliaAlvesSolveExcel please keep up your good work and share with people like me who only able from internet. Thanks again for your hard work

[@wayneedmondson1065]

Hi Celia. An awesome lesson and solution! Love the way you explain and build each component a step at a time.. very clear and easy to follow and understand. You are an excellent teacher! Thanks always for sharing your knowledge, resources and wisdom :)) Thumbs up!!

[@CeliaAlvesSolveExcel]

Thank you for your kind words, Wayne. I am glad that you find value in my no-rush way. ;-)

[@vivekr08]

Thanks Mam for given my query solution, i appreciate your efforts💐 ✨️ & thanks again to cover each and every point precisely👍

[@CeliaAlvesSolveExcel]

You're very, welcome, Vivek. Let us know how the implementation goes on your end.

[@GrainneDuggan_Excel]

Interesting solutions. Thanks. This may be an answer to one of my problems. Thanks Celia

[@CeliaAlvesSolveExcel]

Glad to help, Gráinne! :)

[@yousrymaarouf2931]

You are wonderful and your explanation is easy and smooth and reaches all levels. Many thanks and respect

[@CeliaAlvesSolveExcel]

Thank you very much, Yousry. I appreciate your support and feedback. :)

[@kebincui]

Great video. Thanks Celia👍👍👍🌹🌹🌹

[@CeliaAlvesSolveExcel]

Thank you, Kevin! Glad that you found it helpful.

[@gopichand5717]

You videos are awesome And helpful 😊

[@CeliaAlvesSolveExcel]

Thanks! Glad they help. :)

[@Paladin101]

Thanks for this. I cannot tell you how many hours I've spent trying to find the answer to this on the web. I know it should be easy to find but, in order to find the answer to a question, you need to know what question you're asking and I found nothing on how to break the link to source data in Power Query (until this video) 🙂

[@CeliaAlvesSolveExcel]

Hi, Paladin. I am sorry that you had to spend long time to find the answer and I am glad that I could help. I will add your search string to this video description and also the blog post about the same topic to help more people finding it quickly.

[@unegbucollins1836]

You are the best!!!!!! You are Amazing

[@CeliaAlvesSolveExcel]

And you just made my day! Thank you for watching and leaving your feedback. :)

[@McIlravyInc]

Thank you for this! I need to do this for all tables/tabs in a workbook to create a time capsule of the data. Will the macro do that as written?

[@CeliaAlvesSolveExcel]

Hi! The macro does what we programmed it to do. In this case, to unlink the first table that exists on a sheet named "Report." To unlink all the tables in all the sheets of a workbook, é need to program a loop through all the sheets, and inside that loop another one through all the tables in the sheet.

[@leighcampbell6654]

This is really helpful, my workbook contains about 13 sheet tabs, would you have an example of how to write the loop through code please? @@CeliaAlvesSolveExcel

[@Mahmoudalgindy]

Thanks Celia. First veiwer again 😊

[@CeliaAlvesSolveExcel]

You deserve a trophy! :)

[@TheJoshtheboss]

Great video !! Is it possible to remove the query completely via VBA instead of just unlinking?

[@CeliaAlvesSolveExcel]

That's a good point! Yes, you can. Try this: ActiveWorkbook.Queries("NameOfTheQueryHere").Delete

[@terrymcc7805]

Excellent instruction! Very informal but informative style.l You keep referring to your Telegram channel. What / where is that?

[@CeliaAlvesSolveExcel]

Hi, Terry! I am glad that you found the content valuable. A Telegram channel is similar to a WhatsApp group, but the channel members do not have access to other members contact info. In this case, the channel is set up so that only admins can post. This way, I can share my files and announcements with whom is interested, but it does not get too disturbing to the members as if everyone could post in there. The link for the channel is in the video description.

[@terrymcc7805]

@@CeliaAlvesSolveExcel Thanks so much - All I see is "T0022" but no link - is that the hashtagged one e.g.#excel?

[@CeliaAlvesSolveExcel]

@@terrymcc7805 I am not sure I understand your question. The Telegram Chanel is available at t.me/celiaalvessolveexcel The text file with the VBA code mentioned in this video is on the last post in the channel and also the files section. Let me know if you still need help.

[@beabeeeee]

Awesome! It open me a door of opportunity of various possibility for a task I currently working on. Thank you very much for the lesson! :) One quick question, can I save the sheet as a sheet instead a workbook? or can I save the files on a different folder/ path? sorry for my bad english, it's not my first language🙏

[@CeliaAlvesSolveExcel]

Hi, Bea. When you use VBA to save, you can adjust your code to save a copy the entire workbook or to save just one sheet as a separate workbook. On your VBA code you can also indicate the folder where you want to save the new file. I hope this helps. PS - Your English is good. ;)

[@beabeeeee]

@@CeliaAlvesSolveExcel Oh I See, Thank you very much for your help! You answer very fast! how to do that? Can I just replacing the "workbook" to "sheet" on the code? and do I typed " Save as Filename = ThisWorkbook.path" to Filename="(the folder I want, or even a cloud folders on my gdrive for example?)" Sorry for so many question🙏 PS - I didn't know I can be this flattered when be complimented about my english, thank you very much lol :')

[@CeliaAlvesSolveExcel]

@@beabeeeee it is hard to tell you the exact code without knowing all the details about your requirements and business case. If you spend some time googling about vba and learning about it, you will find your way. That's how I learned as well. As for Google Drive, I would have to test. For sure vba does not work if you open the file online on the browser. It needs to be in the desktop Excel version.

[@beabeeeee]

@@CeliaAlvesSolveExcel I see, thank you again! I will googling them

[@paolosoloperto4948]

Great 👍

[@creelmanc]

Brilliant

[@garethmorrall1047]

This great is it possible to then email the data path of the file to my colleague. Ideally if I saved the newly created unlinked file to onedrive and then with vba email the one drive link. Ideal situation eventually would be if this could be ran every day at 4pm with task scheduler so colleague gets the sheet every day. Want the sheet to be unlinked as query filter on the Today filter. Thanks

[@juliocesarborba633]

You are from Brazil ?

[@CeliaAlvesSolveExcel]

I am from Portugal, living in Canada. :)

[@juliocesarborba633]

@@CeliaAlvesSolveExcel Legal, e eu morando no Brasil, e gostando de suas postagens. Parabéns

[@CeliaAlvesSolveExcel]

@@juliocesarborba633 legal, mesmo! :) muito obrigada, Julio!

[@TheMoh148]

Hi Celia, Thank you so much. My question is: How can we export Power Query Query data directly to a txt/csv file? In case we have more than a million rows *(impossible to load them into excel)*.

[@CeliaAlvesSolveExcel]

Interesting problem. I never did that. My first attempt would be: - load the PQ results to the data model - make sure that data has an index column or a way to filter the data by sections - create a Pivot table with the fields to export to the csv file and the index column field in the filter - program a macro that: - filters one section of data, copies that data from the pivot table and writes it in a csv file - loops through all the filter values and adds each section to that csv file. Instead of copy and paste, use arrays so it is quicker. I have no idea how much time would it take complete for over a million rows. Instead of reading the data from the "physical" pivot table, maybe it is possible to read from the data model directly with VBA. Not sure. I've never tried it.

[@TheMoh148]

@@CeliaAlvesSolveExcel Many thanks Selia for the solution.

[@ramanhai6205]

Will this help in solving refresh issues between windows and Mac ? I am using powerquery in windows and need to share the file to my colleague who uses Mac . He needs to do some changes in the data and need to refresh. There occurs a refresh error and power query stops working. Kindly give an advice on this mam.

[@Mahmoudalgindy]

The file path will be different in Mac. You need to add a sheet for settings and then use Cell() function to make the path dynamic.

[@CeliaAlvesSolveExcel]

Does the user on a mac need to run Power Query? Which version of Excel for Mac does that user have? And to where do the queries connect? Is it to Local Excel or CSV files?

[@ramanhai6205]

@@CeliaAlvesSolveExcel yes mam he needs to run the powerquery. I uses office 365 and I am not sure about his version. Will update you soon. It's connected to local excel file.

[@ramanhai6205]

@@Mahmoudalgindy I cannot insist him to do the file path setting as he shares the file with clients and there will be changes happen from their side also. Its almost like a shared file in which we include few persons input.

[@CeliaAlvesSolveExcel]

@@ramanhai6205 see if this helps: kzbin.info/www/bejne/sGnCYoWuiZhkgLc (start watching where the link takes you)

[@jerrydellasala7643]

I wish I had seen this 2 weeks ago as I had the same exact situation, but I resolved it very differently. The report generator - file with the VBA code and queries - had its own queries that pulled in the 10 tables I needed. I then used this VBA code to DELETE THE QUERIES instead of just unlinking them - the source file wouldn't be available to the final report. Recording a macro of deleting the queries didn't work at all, but on an Excel board someone posted the solution which is this code: Dim q As WorkbookQuery For Each q In ActiveWorkbook.Queries q.Delete Next Deleting the queries does NOT remove the tables. That's just the part of the code that I got to delete the queries. Not sure how to delete them selectively (haven't tried!). I also ran into the refresh issue, and LOVE the addition of DoEvents! I used MsgBox to interrupt long enough for the refresh to finish to avoid the Save problem you had. It worked, but will probably be changed on the next update! Also, the macro is simply saving the file as a macro enabled file rather than converting it to a regular workbook. I didn't realize handling it as you did would eliminate the macros without breaking the running macro. It makes a LOT of sense, but I didn't really dwell on it much. Another improvement for the next update! Really great lesson. Thanks!