none
Script to Combine Files with Dynamic Columns RRS feed

  • Question

  • So I have sales data for a year, separated into 12 workbooks by month (each of the 12 workbooks contain a single worksheet named after one of the 12 months).

    In each worksheet, each unique store branch has two column, one denoting quantity sales, and other sales in dollar value.

    I want to combine all 12 workbook files using power query into a single query. (using Excel 2019)

    Here's the dilemma. Over the course of the year, new store branches open and old ones close.

    Below is a simple example of the way this affects column headers and their addresses over the series of workbooks.

    Jan: [Qty Store A], [Value Store A], [Qty Store B], [Value Store B], [Qty Store C], [Value Store C]

    March: [Qty Store A], [Value Store A], [Qty Store AB], [Value Store AB], [Qty Store B], [Value Store B], [Qty Store C], [Value Store C]

    August: [Qty Store AB], [Value Store AB], [Qty Store B], [Value Store B], [Qty Store C], [Value Store C], [Qty Store D], [Value Store D]

    • In Jan Stores A, B and C exist
    • In March Store AB opens and its columns get sandwiched in between A and B, all subsequent stores have their column address bumped to the right
    • In August Store A closed, so all column addresses get bumped to the left, and store D opens.

    Is there a way to combine the files, so that the resulting query retains data from all store branches, and matches the data sets of store branches over the workbooks regardless of what their column address is?

    Tuesday, November 19, 2019 6:56 AM

Answers

  • Hi

    IMHO this is the same problem as Combining Files From Folder in Power Query you raised yesterday. Proposals were made to the latter

    1/ Have they been tested? Does one do the job?... Please update the other thread with clear indications of what works/doesn't work and if no proposal work as expected please provide an example of the expected result

    2/ For this case/thread please provide an example (based on the above data: Jan, Mar, Aug) of the expected result AND please indicate if your sheets have Tables or not AND if they don't have Tables do their 1st row contain your column headers (Qty Store A, Value Store A, Qty Store AB...)

    • Marked as answer by khizer.daar Wednesday, November 20, 2019 6:22 AM
    Tuesday, November 19, 2019 7:52 AM

All replies

  • Hi

    IMHO this is the same problem as Combining Files From Folder in Power Query you raised yesterday. Proposals were made to the latter

    1/ Have they been tested? Does one do the job?... Please update the other thread with clear indications of what works/doesn't work and if no proposal work as expected please provide an example of the expected result

    2/ For this case/thread please provide an example (based on the above data: Jan, Mar, Aug) of the expected result AND please indicate if your sheets have Tables or not AND if they don't have Tables do their 1st row contain your column headers (Qty Store A, Value Store A, Qty Store AB...)

    • Marked as answer by khizer.daar Wednesday, November 20, 2019 6:22 AM
    Tuesday, November 19, 2019 7:52 AM
  • Hi Lz._,

    Yeah it's the same, some of the answers I saw seemed to misinterpret what I was getting at so I rephrased in this post. I took a look again and saw your answer and it was exactly what I was looking for thanks.

    Wednesday, November 20, 2019 6:25 AM