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!
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!
