Power-Query: Combine Multi. Sheets – recommended Excel Formats (Size wise).

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
668
Hello friends, Hope all is well!

I have a Power-Query, that combines multi workbooks. All is well but because the files are CSV; the power query workbook is heavy in size (can reach 100MB).

Q1: Can Power-Query combine Xlsb files instead? If not, what formats would you suggest?

Q2: What formats that can be used for combining?

Thank you so much in advance!
 

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
668
Sure they are all acceptable by PQ. But the total size of all 14 CSVs was 1.4GB. For testing purposes I saved the CSVs as Xlsx and total size became 680MB.

so I am wondering will the PQ file after combining all the 14 files in xlsx or Xlsb be less in size? Be it PowerPivot or power query?
 

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
668
I am working on Excel 2013 professional plus; I think for some reason PQ doesn’t work on Xlsb. Am I wrong? Is there a workaround that?
Is there a step by step guide on how to combine Xlsb files in PQ?
 

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
668
OK Hi again! I downloaded the add in and it works; thank you VERY much. I made some testing for the sake of creating a more smaller file in size.
here is what I did, and still need your kind help and advice please.

as mentioned, I have a folder with 14 CSV files; they are 1.4GB in size. I created an excel sheet with power query & PowerPivot that is reading from those 14 files (combine from folder via PQ, to PowerPivot. The size of the excel became 105MB.

Is the size reasonable? Can I make it smaller? How?

Would it make a difference if I saved all 14 files as xlsm? I tried that and total size became 680MB) but the Excel is still 105MB).

Your experience, feedback and help is truly appreciated
 

Forum statistics

Threads
1,085,755
Messages
5,385,696
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top