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

[countryfan_nt]

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!

 

[sandy666]

because the files are CSV; the power query workbook is heavy in size

nonsense

Can Power-Query combine Xlsb files instead

why?

What formats that can be used for combining

all accepted by Power Query

 

[countryfan_nt]

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?

 

[sandy666]

why don't you try with xlsb and PQ?
btw. Power Pivot increase size of file

 

[countryfan_nt]

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?

 

[sandy666]

PowerQuery add-in for XL 2010/2013, all above has PQ built-in

 

[countryfan_nt]

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

 

[sandy666]

I created an excel sheet with power query & PowerPivot

try without Power Pivot, who said using Power Pivot is mandatory?