Excel Power Query: Reducing File Size by Deleting Tables?

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • excel.png
    excel.png
    53.4 KB · Views: 3

SuperNerd

New Member
Joined
Sep 16, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,079
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,016
Messages
5,545,512
Members
410,688
Latest member
pije76
Top