PowerQuery Excel File Size greater than using Macro

Question

I have a daily CSV file that is about 350MB. Using a macro to delete the rows I do not need, the resulting .xlsb file is 13MB.

I discovered Power Query for Excel, and I can get the exact same data as the macro (number of columns and rows are identical) the resulting .xlsb file is 40MB!

After a lot of investigating, I believe it is the formatting of Power Query that is the main issue. Removing all formatting from the sheet, the file shrinks to 20MB.

However, I cannot figure out what else might be creating the 7MB difference.

If I convert the 40MB .xlsb file to text, and then back to .xlsb, the file size ends up being 13MB.

Answer 1

In Power Query, it might help to load the data to sheet and not the Data Model?

If you get the same size difference when saving to XLSX, you could try "unzipping" the file before and after zipping by renaming to .zip and seeing what is different in the archive?

If nothing else helps, you could try sending a frown from Power Query with the problem.