I'm looking to combine 52 tables (weekly scrap reports from our ERP software) using PowerQuery. The column headers (see attached sample) are the scrap codes recorded during the week being reported. Therefore the number of columns in each table varies with what issues were found. I also have a summary table (with one dummy record) that has all available scrap code columns.
My approach was to use the summary table as the main starting point and merge the other 52 tables into that one. I've tried both Merge and Append without success.



Using Merge:
1) software wants me to "Select the same number of columns from both visible tables to continue", not practical with over 300 scrap codes in the master table (yes, that's way too many codes, but that's what's in the system)
2) this process only merges two tables at a time (I've got 52)

Using Append:
1) this process will merge multiple tables but only the first four columns

I downloaded PowerQuery today and don't have any experience using it. Any advice would be highly welcomed.

NOTICE: cross-posted on MSOfficeForums and ElsmarQualityforum

Attached Images

PowerQuery question.png (27.7 KB, 12 views)

It seems to me this has the same solution as a question I read recently. The difference is that he would have had to create only five extra columns and do a VLOOKUP in each one, whereas you have to do a SUM referring to 52 different arguments. Maybe someone could suggest how to get there by emulating a three-dimensional table, but I've never done that and wouldn't know how to make it work.

I would certainly do this in VBA—much simpler. But if you don't know how to program it, and wouldn't care to learn, I don't know how to help.

If you would care to learn, let me know and I'll coach you. But you would need more than a few days to do it, unless you already know some programming and just need a few hints. Well worth the trouble, I think, but it's your call.