Forum Discussion
Workbook link to current folder
abyerley Okay the example is attached. I've tried to make this as dynamic as I can so if the filename or location of the MasterFile changes, the query will update accordingly. The attached workbook is the one which consolidates row 10 from each of the files within the same folder:
You can see more easily than the end product how this query works to consolidate data from every workbook in the same folder except the master file
And then, of course, the data transformation steps to merge the columns and eventually transpose them into columns as opposed to rows as shown above. If additional files are added to the folder, they will automatically be transformed along with the others.
I believe most of the query will work up until the "Inserted Text Between Delimiters" step. These steps are very dependent on your naming convention of your files so it will need to be updated accordingly. You might consider making a copy of the file (one for implementation and the other for reference). Then hold Alt while opening the Excel application to start a new instance. This should allow you to open the query editor in both documents & step through the query of the reference file, while making necessary adjustments to your implementation file steps highlighted below.
Once configured correctly you can see the output is 42 columns comprised of merged data from the 21 files:
Hope this helps and good luck.
DexterG_III Thank you! I'm not familiar with these query functions, so I'll take a closer look. Much appreciated.
abyerley My pleasure, although I admit I've offered nothing tangible. Only because of the vast possibilities - the capabilities are numerous and in some cases profound but are very situation specific.
For example, I remember that an inventory file was saved in the same folder every day. In order to plot inventory through time (as each file is always an independent snapshot), I relied on a lot of VBA code and macros to determine whether a new file existed and, if so, how to append it using the legacy import process. This situation today requires no VBA, and every single file within a folder (given the same layout and headers, etc), can be automatically consolidated into a single table.
If you're not familiar with PowerQuery, it might appear daunting without any further instruction. I'm willing to help more if you are able to share additional information (e.g. example files without any sensitive information and specifics about what components you are looking to extract from each of these files).
Best of luck,
Dexter
- Thank you Dexter. Here's what I'm trying to do (knowing that there may be a more elegant way to do it): I have a series of folders that contain 21 data export files (also .xlsx). I need to capture one row from each of those files (always Row 10) and parse it into two columns in the destination workbook. (From there, the workbook does what it needs to do.) Additionally, I need the workbook to function as intended when copied into another folder of 21 data files. I hope this makes sense, and thank you again if you can guide me further.
abyerley Okay thanks. I'll set up an example query.
What is being parsed in row 10, if I may ask, so I can include in the query example to get you started?
Dexter
- Essentially, row 10 of the first data file is turned into two columns in the destination spreadsheet. Cells A10, C10, E, G, etc. become column 1, Cells B10, D10, F, H, etc. become column 2. Row 10 of the second data file is parsed in the same way into columns 3 and 4. And so on.