Hi,
I have an excel file that pulls data together from various different sources and performs some calculations. The file consists of around 650K lines. When I convert all formulas to values and save the file it is 133mb which is impractical.
Is it possible to create a query the file and then somehow transform the query to a static table and then hold it in PQ as a connection only? Would this work? Would it give a reduced file size? Are there any alternatives?
Thanks
Bax
Hi Bax,
You'd be best to keep the source data in a separate file to the analysis file. You can leave the formulas in this file if you want. Then get the data (Get Data From Excel File) with Power Query as a connection only and then load it into Power Pivot.
If the formulas can be moved from the source file to Power Query then this would result in a smaller source file, and the Power Query file should be more efficient at calculating the formulas than the Excel worksheet. Also, Power Pivot is much better at compressing data than Excel Tables and can handle millions of rows.
Form Power Pivot you can analyse the data in PivotTables etc.
Mynda
Thanks Mynda I will give that a try.
