Excel Power Query: Reducing File Size by Deleting Tables?

[SuperNerd]

I have a fairly large file (276MB, 13+ Queries, lots of data). I'm trying to increase speed/efficiency. My data is broken down as far as it can go, with 90% of it as either a 1 or 0 in the fields. Excel is starting to crash and corrupt the file when I try to do anything- and I haven't even started doing the complex pivot tables yet!

Here's what I did:

1) loaded a large flat file from CSV into excel.
2) split that up into multiple sheets by grouping common data that made sense to be in a table/query together.
3) Did minor manipulations, copy/pasted as values only, deleted the original formulas as well as any unnecessary data/columns/tables... Turned each of those into separate tables
4) Data --> From Table
5) From Power Query- Load & Close as Connection Only and Saved to Data Model.

Result is 13 Queries, all loaded into the data model and connection confirmed. I then created relationships and saved.

Now, It's my understanding that at this point, since all my data is loaded into queries, as connections only and saved in the data model- that I can delete the original tables in the excel worksheet, and that would significantly reduce my file size/resources. If I need the data, I can just create a pivot table of it and get it all back- no reason to keep it all onto separate sheets.... right?

But when I go and delete out the original tables/sheets, the queries/connections break and I have no access to the data any longer...

What am I missing? What can I do to compress this down as much as I can?
Thank you!

 

[SuperNerd]

To add to this-
The data model loads the data into memory, where as using the connection only excel would need to access each csv file for every query. For complex, large queries, the I/O and system resources could drastically impact efficiency, couldn't it?

 

[sandy666]

So when would you want/need the data model?

Practice

The data model loads the data into memory, where as using the connection only excel would need to access each csv file for every query. For complex, large queries, the I/O and system resources could drastically impact efficiency, couldn't it?

it depends on your code (M)
or how your model will be organized

one additional point: Data Model is LIMITED to 2 GB only