Hi Celia. Thanks very much for this video. I've been wondering about the correct and most efficient way to do just what you demonstrated. So for me.. very timely. As always, thanks for your great videos, content, tips and tricks. I always learn something new at your channel :)) Thumbs up!!
Very good. Thanks. I have been using the Adv Editor to copy the actual code and then store in a folder of text files. Now I can create a Master Query EXCEL file and store my useful queries in that instead. If anyone does this remember to switch off refreshing queries on opening the Master file otherwise it will take an age to open...
Great stuff Celia, I never realised you could copy queries so easily. It especially great that dependant queries get copied over automatically too. Thanks for the video 👍
Thank very much for this video. It solved my age old question on how to copy a query in PQ. I also like your earlier two videos on Excel Automation through PQ and VBA. I like your style of teaching.
Thank you so much for this clarification. More still, I will like to know how to copy a power query that the source is a combination of current workbook. Kindly help meet this
You can copy the queries the same way, but then you need to either copy the data from the first workbook to the second (and maintain the ranges and table names), or edit the step Source in the second workbook and change the M code to refer to the first workbook.
Does it matter (connection & speed, 100K rows x 20 columns mainly Transactions numerical data) if I put Power Query table in a different workbook from the original data? If it does NOT affect speed that much, I can reuse the PQ table and point to a different data source. Thanks (Windows 10 x64, i7, 32GB RAM, OneDrive for Business)
Well, not just liked it also will definitily use it ! I am watching all your videos retroactivly in free time, and finding missing peaces of puzzel and getting many many ideas for my project that I started last month !
@@CeliaAlvesSolveExcel if you are interested in my project, I can short outline you in 10-15 sentences per email on your homepage and give you short feedback once a month what exactly I used from videos. My project should be finished by summer 21, as I am internal stuff of the company and doing operative controlling tasks, same time learning and implementing, so slow pace not consultant mode.
@@bojanjovanovic756 it doesn't matter the time you take. In fact, I think you are in a great position to build a great Excel solution because you know and understand the business requirements. If you are willing to debriefing me about your accomplishments every now and then, I would love to hear from you.
Send the file by email or using a shared location in the cloud like Dropbox or OneDrive. You may need to send the data source files too if the data is outside the file containing the queries. If that's the case, you will have to update the queries with the new data source location.
Thanks for the reply and yes i think i have to update the new data source location because when i try to run the query i get erreor that the source can't be found.@@CeliaAlvesSolveExcel
Thanks a lot for this I have a question If we save the file with another name ( in otherwords we will copy one file) and then alter the editing the source file Will that work. PLEASE INFORM
I am not sure if I understand your question. When you copy a query and paste it, the M code goes exactly as in the original query. To make the new query refer to other data source, you need to edit the source name or path in the corresponding step where that source needs to be referred to. Does this help?
I've been looking for help with a related question: #1 - How do I copy multiple Applied Steps in one swoop (instead of one at a time) in Power Query from one worksheet to another (same workbook)? Rather not having to move it to another workbook and copy it back. #2 - Or Should I copy the whole Query from the left pane, rename it, and delete the Applied Steps that I don't need? I'd rather do #1 if I could because they're usually the last 6 steps and less room for error. Thanks.
Hi, I want to implement a PowerQuery Transformation in our worklfow pipeline but I am not sure what's the smartest way to do so. Basiscally all starts in a web application where we get a specific .xlsx as output. This is going to be used for a CAD application but to be machine readable it has to be transformed first - this is where I use PowerQuery. The file is later on saved as a new .xlsx and utilized further on. So my question is how can I automate this step of manually opening the file, apply the power querry, save the file again. What are the best approaches to be able to achieve batch processing functionality?
There are many questions involved to be able to answer this. Some of the questions are: where are the Excel files saved? Server, Sharepoint, other? Which version of Excel are you using? What's the frequency in you need to run this process? When you say "batch" are you referring to preceding multiple Excel files at a time?
I guess I found a way by using table data as input parameters which at least reduces the amount of steps involved. This does the job at the moment, but probably there are smarter ways to do this. So the files are stored on OneDrive, using Office 365, frequency is low, actually it's all about usability for other users and the input is always just one file at a time.
Hi Mam i want to known how to save power query final result file with unlink query connection? Here i have a answer first need to selected table then go to data and click " Unlink " button. But can i do it automated this step without click unlink button please guide🙏
Hi, @Vivek Rane! First of all, thank you for your question and your answer. I didn't know about the "unlink" feature and I just learned that from you. Second, I don't think there is a setting to automatically unlink the table from the connection. To me, it makes sense that this setting may not exist because this would defeat the purpose of Power Query which is to allow the user to easily refresh the query results when needed. That being said, I can imagine a few scenarios of why you would want to do this and I may have a suggestion for this challenge. Can you explain why you want to unlink the table from the connection? Is it because you want to send the query results on a separate query to another user? Thanks.
@@CeliaAlvesSolveExcel thanks for respons, yes your are right about power query working that's why we use this every day same work to do on just refresh But, if i want to save everyday ( it's my regular activity for each day)that result in new workbook with new name, so under that new workbook also showing query connection so in same new workbook if i add comment or data difference remarks and save n close, but if some one just refresh that new file my all comments are gone, that's why i totally disconnect all connection and mention just result so i can maintain my comment 🙏please help if you have solution for this
Hi,@@vivekr08. I prepared a video to reply to your question. It is being edited and should be published in the next few days. Stay tuned and let me know there in that video comments if it helped.
Hi, thanks for the video! The problem I face is that as I copy multiple sheets from one workbook to another, every time I copy a separate sheet that contains the same function (actually every sheet contains two tables one source table and one output table through the same function), excel creates a new query for the same function and appends a incremental number to its name. Finally, the new workbook has multiple copies of same query. Is there any way to troubleshoot this issue? Thanks!
Thank you for your question. I need to understand your scenario better. On the new workbook to where you copy those sheets, what would you like to have? The tables only (input and output tables on each sheet) without the queries going with it? On the original file, how to you make one power query function only generate multiple tables?
hello. i am trying to do something similar to move a pq excel sheet from one work book to another... just to confirm if you right click on the sheet, select move or copy and click on copy to another excel workbook, it will copy all the queries to the new workbook
It should copy all the queries associated to any power query table you have in that sheet. But you can test. Create a copy of your two workbooks and test moving the sheet to see what happens.
Hi Celia, Thanks so much for this video. So would this technique work if my source data is changed? I have a situation where I get source data on monthly basis. I use power query to modify the excel and prepare reports. How can I use the same query on the new source file?
Hi, Salman. There are different posible scenarios. Is your query on the same file where you have your data or on a separate file? When you receive new data, where do you save it? Do you replace the previous data source file? Do you save it on a dedicated folder together with the previous data source files?
@@CeliaAlvesSolveExcel Thanks for your quick response. It's a monthly excel file downloaded from system. Each month the file is named by the month. For example Jan 22, Feb 22... Now if I do all the work in Jan 22 file my question is how can I reuse the same for Feb 22 file? And yes the monthly files are stored in the same folder.
@@salmansadatkarim ok. I have a few more questions to understand your scenario. So you have the data source files in one folder. Where are the queries located? In each data source file pointing to a table or sheet in that same file or on a separate file? There will be more questions after your answer. :)
@@CeliaAlvesSolveExcel Thanks Celia. So after I receive the main file from source (monthly) I perform some routine tasks which is similar to running a macro but I find Power Query faster and easier. That said the queries are within the source file. Since I am new in query I think I need to bring some changes in the files and organize my tables to repeat my queries. Will wait for your more questions.
@@salmansadatkarim there are several ways of working and it all depends on several elements of the work flow and what you need at the end of the process. Your data source within the data source file, is it a sheet, a table or a range? How do started your query? Data tab > Get Data > From Table/Range? If your query Data source is a table, when you copy the query to another workbook, it will work without changes to the query as long as you name the table in the new file the same as in the original file from where you copied the query. If you go Data > Get Data > From File and then point to the current file and select a sheet, your query will get the file path hard-coded in the M code and the sheet name. That path will need to be changed when you copy your query over to a new workbook because the path will be different according to the new file name. The sheet name where the data is also needs to be the same for the query to work. That being said, another way of working could be: place your Data source files in a folder. In a separate file create a query that connects to the folder and brings in the data from the most recent file only. After the query transformations, you can copy and paste the query table into a new sheet in your Data source file or a separate file, or a template file where you have the calculations and report ready for what you need to do with your data. These are some of the options. The best approach will depend on many factors of your procedure.
You have at least these two options: 1 - copy the Table to the workbook to where you copied the query and make sure it maintains the same name as in the previous workbook. This way, the copied query will work. Or 2 - In the new workbook create a query to import the Table from the orinal workbook. Let's say you name this query "ImportedTable." Then, go to the query copied that was referring to the table and select the step Source. In the formula bar, delete what's in there and replace it with "=ImportedTable" I hope this helps.
@@CeliaAlvesSolveExcel Sorry for responding so late. In my case, I'm using Power Query here to format large reports that are produced by an application. Therefore I want to use the same formatting for each report I generate. What I'm trying to say is that there will only be one dependent table in each of these, itself. What I'm doing is using Power Query to condense a lets say 30 column report into 12 columns and clean each column by changing the data types, filtering and column headers and arrangement. I've got over 40 client reports I need to clean this way. This is part of my workflow before each meeting with clients. Power query would be a great way to automate this rudimentary task. I am still super confused as to why I would need to import a table. Apologies for my ignorance regarding this.
This is especially frustrating as Power Query automatically names the report range Table1 in source for all instances. Yet when I import the query that has Table1 it comes up with that error. Its difficult when you don't have a background in programming to decipher what its saying as its never asked in plain English everybody understands. Lol!
