Forum Discussion
Appending data using power query
ajl_ahmed I would take a new workbook and connect to the relevant sheet in each of the workbooks by using the "Excel workbook" connector. Then you can append one query to the other. Close and Load and you'll end up with one single table, leaving the two original files untouched.
By the way, I assume you are on the Insider Beta channel.
Does this method work if I make many copies of workbooks and want to send the data of specific sheet into a master workbook? I mean that can I make this method as a property of workbook such that it work even I make copy of workbook.?
ajl_ahmed Perhaps I don't understand the question but PQ doesn't "send" data into a master sheet. The process that I described creates a table in a new workbook (you may call that "the Master") and is connected to the underlying original files. If these files change (without renaming the file name and the sheet name) the Master will automatically pick-up the new data upon a refresh of the connection.
Once you have set this up correctly, you could make it dynamic by including some variables that allow you to write the full file paths and file names in the "master" and have the query connect to and append those two files.
Sorry for my language because English is not my first language
I think what you described above it is useful for me and that is what I want. Whatever the number of folders that I have. The excel files which are included in these folders should not change their name in addition the names of sheets should not also change their names. Then the master file collects dynamically the information included in a specific sheet of these excel files. Can you please help me to do this probably I could not do that. I have attached all the files required (1,2,3,4 and Master) the master takes the information from the sheet "Success Students Rank (Final)" of excel files (1,2,3 and 4) and collected them in the "All success students" sheet of master excel file.
Hope I express myself well this time.ajl_ahmed Thanks for uploading the files, but allow me to ask a few more questions.
1) How familiar are you with PQ?
2) Are you wanting to combine 4 files or all files in a folder?
If the latter, you can't do that with PQ on a Mac, as easy as you can on a PC where you can connect to all files in a folder. That's not (yet) supported by PQ for Mac You would have to add a query for every file you add to the folder and include a step that appends that particular file to the others. Doable but not very effective.