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.
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
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.
abyerley Just to clarify, you'd like the extracted data in a single row (42 columns in total or 21 files * 2 columns) rather than have 21 rows with 2 columns?