#1
|
||||
|
||||
Power Query to combine multiple tables of unequal number of columns
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 |
#2
|
||||
|
||||
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combine multiple tables into one pivot table in order to do trend analysis | marelisev | Excel | 2 | 04-15-2017 08:30 AM |
Query and Power Query not working Excel 2016 | bl10 | Excel | 0 | 07-22-2016 06:25 PM |
VBA: Selecting columns from multiple tables | LouterMattheus | Excel Programming | 1 | 07-22-2015 06:33 AM |
Copying and pasting values with unequal data columns | grexcelman | Excel Programming | 5 | 12-05-2014 11:36 AM |
Formatting multiple tables and columns efficiently? | table_column | Word Tables | 1 | 07-02-2012 05:55 PM |