Is there an alternate way to look into an excel sheet instead of connections and queries?

[SPS41]

I have 2 workbooks. One is a Bill of materials (Bom) and the other is a master list (Ml).
My bom takes the Ml as a connection, manipulates the data through power query and spits in on a new worksheet in the Bom workbook. I then open a new sheet (within the bom workbook) perform a vlookup and copy needed information to it. All this is on the server. And my file before the connection was 400 KB and now after the connection and power query is 1.6MB. Is there a way to go around having this connection and power query making the file big. I have seen posts talking about creating a query using it for the task and then deleting it. But that makes the file big and majority of the task happens when the file is big.
Is there a more efficient way to tackle this? Or am I overthinking this?
Please let me know if you need any other info.
Any help is greatly appreciated.

 

[MARK858]

Is there a more efficient way to tackle this? Or am I overthinking this?

1.6MB isn't a big file

 

[SPS41]

1.6MB isn't a big file

Yeah I agree but it’s a lot bigger than before. I was just wondering if there was a way instead of loading a whole master list of about 10k rows on it. Is there a more efficient way? The file tripled in size. Is there a better way to go about extracting this data from the master list?

 

[JEC]

The file size is probably not large bc of the data. It depends on how you use that data in formulas etc.

-How does your formula look like?
-How many rows contain formulas,
-Do you refer to whole columns instead of used ranges?

Or create a copy of your file. How large is that file after deleting all the formulas?

 

[SPS41]

The file size is probably not large bc of the data. It depends on how you use that data in formulas etc.

-How does your formula look like?
-How many rows contain formulas,
-Do you refer to whole columns instead of used ranges?

Or create a copy of your file. How large is that file after deleting all the formulas?

I haven’t done anything crazy in power query, just reordered some columns, and created a custom column which removes spaces and special characters.
I then click close and load and that creates a new worksheet called modified master list. I then use a macro to use vlookup to copy data from the modified master list sheet to other sheets.
When I deleted the connection and the modified master list sheet it went back down to 466 MB.

 

[JEC]

it is not bc of what js happening in the query. Do you paste vlookup formulas in the sheet with vba? If so, convert them to values to remove the formulas.

Also, go to the last filled cell and try to delete all emtpy rows. Maybe your vba script is the issue, creating unnecessary data below your table.

 

[SPS41]

it is not bc of what js happening in the query. Do you paste vlookup formulas in the sheet with vba? If so, convert them to values to remove the formulas.

Also, go to the last filled cell and try to delete all emtpy rows. Maybe your vba script is the issue, creating unnecessary data below your table.

Let me check and get back to you. I’ll also post some of the code in the next reply, in case I miss something. Thanks!