PowerQuery

[JFuller]

Right now I have an enormous amount of cells that use VLOOKUP to reference an external workbook. I think this is slowing me down tremendously. Someone told me to look into powerquery. I think by doing that, I can create a query and link in the external 'pricing' workbook so my formulas dont have to reference the external each time.

file 1 - spreadsheet.xlsx - I want to put in the item code and have the size, description, and price pull from the query table

• cell A2 is a user input (item code)
• Cell B2 would look at A2 and pull the corresponding size
• Cell C2 would look at A2 and pull the corresponding description
• Cell D2 would look at A2 and pull the corresponding price

file 2 - pricing.xlsx - I want to import that as the query and be able to pull from it

This should increase my speed, correct? does the query table update automatically?

Thank you!

 

[GraH]

In Power Query you join both tables. Depending in the situation one could argue you can load those tables directly into power pivot. A measure could be even faster.
It might be wording, but a query does not update automatically. You need to refresh it or program a VBA event.

 

[SpillerBD]

A Query can be made to update automatically. The default for queries is to not update automatically.

In the Query Properties, you can choose to update when opening the file. There is also an option to Refresh Every XX Minutes.
You can also refresh the Query if it is tied to a Pivot Table by having the Pivot Update the data source on opening.

 

[JFuller]

Is that the option I should be exploring to store a table (5,000 ish rows) in my excel file and not have to reference to an external source with index/match? In order to speed up the file?

 

[SpillerBD]

Is that the option I should be exploring to store a table (5,000 ish rows) in my excel file and not have to reference to an external source with index/match? In order to speed up the file?

The Query, loading new/changed data in the Data Model, is the "slow" part. After loading, you can disconnect the Query altogether if desired. 5000 rows should be about two blinks.
Using the Power Query to yield a "LOOKUP" is lightning fast compared to any of the worksheet functions to do the same and scales up much better.
I had fun comparing VLOOKUP on 50k+ rows. That was a wow moment.
The Analysis in PowerPivot (or even a Standard Pivot) is lightning fast. PowerPivot gives you more flexibility than a standard Pivot Table.

 

[JFuller]

My issue is this is a bid template. So we're using a template to bid our jobs, and then the external sheet is a pricing sheet that gets updated weekly. Currently I just drop a new pricing sheet and overwrite the old one, and all my vlookups in the bid template still reference that pricing sheet. if i'm creating dozens of bid templates every week, then in order to update the pricing in each one i'd have to somehow copy that data into each bid template. So thats why we've been using one central file with all of the pricing that each sheet pulls from.

 

[SpillerBD]

The Query Method would be able to update from the file in much the same way, but with much better performance. You can integrate the Query into a Template .xltx or .xltm file.
To simplify the updating, you will want to use the Query Properties Option, Refresh this connection when opening the file.
If you are tying the data into a Pivot Table, you will want the Pivot Table to Refresh on opening the file.

If the bid template is being sent out of your LAN-Network, the data connection will break and updates will not occur.
If you need to lock the file to avoid updates because the bid has been accepted and locked in, you can fix the values by removing the Query and the Data will remain and not be updated.

Depending on the need for transparency, the data loaded from the Query may be loaded to a Table or not. If the data is not loaded to a sheet, it could still be seen by the drill-down function of a Pivot Table.