Running out of memory (Exceeding 400GB file)

[ste4en]

Hello, I am running a power query on three Excel workbooks, each have about 200 sheets. I am pulling about 70 rows and 30 columns from every sheet. I was unable to perform any filtering or column removal in the process as it just errors out. Anyway I have decided to let it run as is and i do the filtering etc after all the data is in one large flat file. Overall about 70,000 rows.

My problem started today (the spreadsheet grows daily and is almost complete, maybe another 5,000 rows to go) in that it gets to about 60,000 rows and my computer runs out of starage. the file size exceeds 400GB.

It doesn't seem right that the query is so big while it is being created. it is also taking about 30 minutes to run.

Any way to speed this up or let it use less memory.

Thanks = query below.


-----------------------------------------------------
let
Source = Folder.Files("C:\Users\sh\Desktop\Gasifier work\Restart Excel Sheets"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content],true)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Name.1", "Data"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Name", "FileName"}, {"Name.1", "Name"}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Renamed Columns", "Data", {"Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column2", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column3", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column4", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column5", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column6", "Column60", "Column7", "Column8", "Column9", "SG Solutions - Routine Maintenance Outage Cost Estimating Worksheet"}, {"Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column2", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column3", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column4", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column5", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column6", "Column60", "Column7", "Column8", "Column9", "SG - Routine Maintenance Outage Cost Estimating Worksheet"})
in
#"Expanded Data"

 

[Matt Allington]

It’s not a lot of data, but Excel as a source is not ideal. I realise that’s not in your control of course. Try processing one file only and combining into on sheet. Do that 3 times, then combine the 3 workbooks.

 

[ste4en]

Thanks - I split into three, not ideal but it works.

 

[Fluff]

Cross posted https://www.excelguru.ca/forums/sho...mory-(Exceeding-400GB-file)&p=38537#post38537

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.