Hi guys,
I have a small problem, and you would love some inputs on how to find a smart solution.
So I have multiple workbooks (10 plus), and each woorkbook has some data. Let us just call them datasets.
When I want to investigate something, create a report or create an Excel template I am using data from multiple datasets (workbooks).
Just so I can give you an example: One workbook can have prices, an other can have a forecast, yet an other can have a status on how many of each product we have on stock.
Now these datasets (workbooks) are being regularly updated. Let us assume, I want to make a graph or some kind of template, which include prices, forecast and stock. What we are doing now is, that we make vlookups on these datasets/workbooks which are located on a common driver.
This is working fine, but the problem is that my template become so heavy. It crashes multiple times. The reason is that my datasets have a lot of data, and I guess that vlookups are pretty heavy on Excel.
My question is: Do you have any ideas to make this more efficient?
My thought: I am thinking of creating a new workbook and gather all datasets in power query (Get data). Then I can connect all these datasets with a common product ID.
Do you know if I can make a lookup in a table which is in power query? and will this be less heavy? Otherwise I will need to create pivot tables in different sheets?
Please, give me some inputs
All ideas are welcome
I have a small problem, and you would love some inputs on how to find a smart solution.
So I have multiple workbooks (10 plus), and each woorkbook has some data. Let us just call them datasets.
When I want to investigate something, create a report or create an Excel template I am using data from multiple datasets (workbooks).
Just so I can give you an example: One workbook can have prices, an other can have a forecast, yet an other can have a status on how many of each product we have on stock.
Now these datasets (workbooks) are being regularly updated. Let us assume, I want to make a graph or some kind of template, which include prices, forecast and stock. What we are doing now is, that we make vlookups on these datasets/workbooks which are located on a common driver.
This is working fine, but the problem is that my template become so heavy. It crashes multiple times. The reason is that my datasets have a lot of data, and I guess that vlookups are pretty heavy on Excel.
My question is: Do you have any ideas to make this more efficient?
My thought: I am thinking of creating a new workbook and gather all datasets in power query (Get data). Then I can connect all these datasets with a common product ID.
Do you know if I can make a lookup in a table which is in power query? and will this be less heavy? Otherwise I will need to create pivot tables in different sheets?
Please, give me some inputs
All ideas are welcome 
