Combining Data from Multiple Tables

[TylerDove]

Good morning,

I am working on a project that requires me to combine the information from 7 different tables in Matrix format into one master table in tabular format that acts as the data source for a PivotTable. The solution needs to be something that can run every time a user has finished their session of editing the information in the table rather than a 1 time data modification.

I had initially solved this by creating 7 distinct queries from each of the tables using Get & Transform (the 2016 embedded Power Query), appending them together and then using the "UnPivot" option to transform the data from Matrix to Tabular.

This solution resulted in Excel taking 45 seconds to load every time the sheet was opened. This workbook is a pretty critical part of the workflow at my place of work and I cannot have it take 45 seconds to open every time a teammate needs to update information.

My next idea is to write a VBA script to do the appending and from there use Power Query to "UnPivot" the data. Is this a good approach. If so can someone point me in the direction of a good thread for this?

I am using MS Office Pro Plus 2016 on a windows machine. As I am new here I am unsure what resources would be needed to best answer my question so please let me know what other information you need.

Thank you in advance.

 

[Fazza]

why not just make the pivot table directly from the 7 different tables?

likely some sample data would help understanding

 

[TylerDove]

Thanks for the idea Fazza!

I tried your suggestion to draw from multiple tables for the PivotTable. I think it's moving in the right direction.

I have attached a sample of the kind of information I'm dealing with.

http://www.mediafire.com/file/3xoac3yd6i92tny/Sample_For_Mr._Excel_Help.xlsx

The project is to allow different users to choose when they want particular products to be ready (ie how many units do we expect to move of a given product in a given week). The information from all users then needs to be aggregated into a "Master Sheet" that shows total quantities of each product for each week.

This is a working document that will have new products added and removed all the time. The sheet must be dynamic in order to allow for users to add timing for new/different products.

If there is a way to accomplish this using your suggestion, I'd love to hear more.

As mentioned in the original question, PowerQuery did everything I needed it to do, it just took 45+ seconds to open.


Thanks

 

[Fazza]

any chance of some sample data posted directly to the forum?

a pivot table can combine data from 7 sheets
and can automatically pick up extra rows as data is added if the data source references the sheet name
via appropriate SQL data can be aggregated/filtered/re-arranged

I don't use powerquery but if it takes 45+ seconds to open then how much data do you have? A pivot table is likely to take a while too

the pivot table you tried, did it do all you need? If not, can you explain what still needs doing?

 

[TylerDove]

I'd gladly post the sheet to the forum. Could you point me to how I would do that?

 

[Fazza]

AFAIK that can't be done. please have a look at the forum guidelines. and maybe the FAQ

there used to be an add-in for posting info from a worksheet - I haven't used it for many years & am out of touch with what is current

there is a test area too, btw, so you can check if things are posting as you're expecting