Best practices to reduce file size after consolidation/append of sheets

[Adnandos]

Hi there

I have a file where I appended 6 worksheet tabs into one power table called consolidated which only combines several selected columns.

My resulting file is 50mb, before consolidation/appending it was about 38mb.

Should I be deleting the separate worksheet tabs once I have them all in the consolidated tab?

i.e. What is the best way to simply keep one worksheet in this file? And as compressed as possible?

Thanks in advance!

 

[Ed Kelly]

Make sure you only bring in required columns everything else column-wise would be removed. Also consider filtering on time if say your data base runs to 3 years and you only need 1 then filter out the un-required years. Also if the data set is heavily text ladened it will bloat your file and thus on occassions power pivot with fat short lookup tables and one long thin transaction table is the way to go (or better still Power BI).

Knock 'em Dead Tiger!

 

[Adnandos]

Hi there, few additional queries and or responses:

"Make sure you only bring in required columns everything else column-wise would be removed."
- Are you referring to the excel tables or to the power table?
- In the power table I've selected the only columns I need, are you suggesting that the excess columns be removed from the excel tables?
- I probably am using wrong terminology over here. For now, excel table to me means raw table data, and a power table means a table put together using power query. And in my specific case, the excel tables and the power tables are in the same workbook.

Point noted on being heavily text laden, will try to unformat as much as possible.

If I may ask the following using my above terms, if I have a power table that I'm happy, is it okay to delete the excel tables? Do I need to do something after deleting the excel tables so that there is no "linking" error?

 

[Ed Kelly]

Adnandos

Are you referring to the excel tables or to the power table? - Lets call them the Excel Table and The Query(in PQ). You only want to include the key columns in your Query, thus you will bring the entire table in from excel and when in PQ you will remove columns that you do not need and indeed rows that you do not need.

In the power table I've selected the only columns I need, are you suggesting that the excess columns be removed from the excel tables? - no as per 1 above (keep in mind you are sculpting the original excel table into a query that you want to work with (that ultimately becomes a new second table). Referred to as ETL extract/Transform/Load

I probably am using wrong terminology over here. For now, excel table to me means raw table data, and a power table means a table put together using power query. And in my specific case, the excel tables and the power tables are in the same workbook. - would suggest, given the size of your existing excel file believe it is about 38MB (thats a bigger puppy) that you consider using from file/ from workbook or from file/ from folder and ultimately capture the query (the 2nd new table) in that new workbook. So you will have the origi
nal workbook 38M and a 2nd excel workbook 15-20MB that houses the query which is now the new table. You will then possibly pivot table this puppy to death or something else... Also note that the connection combined with the refresh capability will mean any new data in that file will get pull across on a refresh or if using from folder any new folder contents (folder should be future proofed) will get pulled into the query

If I may ask the following using my above terms, if I have a power table that I'm happy, is it okay to delete the excel tables? Do I need to do something after deleting the excel tables so that there is no "linking" error?[/QUOTE] - no deleting please, else when you refresh (which is one of the key reasons you would use this stuff) your query table will fall over.

 

[Adnandos]

The recommendation to run the queries in one separate excel file is definitely a much better idea, and has potential to remove many steps in the consolidation of all my monthly files. (initial plan was to run monthly queries, and thereafter consolidate all those queries into one master consol query, clearly inefficient!).

I will do some homework on how to "future proof" folders.

Thanks again!

Sent from my SM-T813 using Tapatalk

 

[Ed Kelly]

No worries future proof is normally a 2 step process to ensure that only a particular type of file is recognized in the query everything will be ignored. So firstly change case sensitivity to lower case then secondly filter on a particular file type .xls, .csv etc. This ensures that if someone accidentally drops a word file into a folder you are using the query does not fall over

 

[Adnandos]

Fully understood on the recommended steps, really enjoying the power of power query. A new file gets thrown into the folder and everything updates in the query, amazing.

I've separated my query into it's own workbook, the source files are around 200mb collectively, the query file is only 5mb, magic!

Thanks for all the assistance.

 

[Ed Kelly]

Fully understood on the recommended steps, really enjoying the power of power query. A new file gets thrown into the folder and everything updates in the query, amazing.

I've separated my query into it's own workbook, the source files are around 200mb collectively, the query file is only 5mb, magic!

Thanks for all the assistance.

Adnandos believe thats called a "come to Jesus moment!"

Keep in mind Excel is a porche and the vast majority of people don't take that puppy out of 1st gear, in fact they don't even get that there is another 5 gears to play with and it is in these 5 gears where all of the fun is. And once you figure out how to really drive this porche you can perhaps for the first time ever start adding real value to the organisation (Let Excel do the heavy lifting and that frees up you time to creatively think and add Real Value to the Senior People in the Organisation.) And a key part of a finance persons role is to take data and convert it into information and then take that information and visually represent it (because a picture does indeed paint a thousand words.)

In time if you love the extra gears that you are beginning to play with in excel you are really going to love Power BI, it is Excel on steroids and its made by the same company!

Knock 'em dead Sailor!