Forum Discussion
Fast Load "export" to Excel
Hi JustDavid
Thank you for reaching out to the Microsoft Fabric Forum Community.
ralf_anton Stachu MarkLaf pcoley Thanks for the inputs. Suggestions from users are valuable.
The slowness is mainly because Excel has to write millions of rows into worksheet cells, which naturally takes time. Power Query preview feels fast since it only shows a small sample of the data, but when loading, Excel must write the entire dataset. To improve it slightly, you could ensure the filtering happens in MS Access (using saved queries) so only the needed rows are retrieved, set Excel calculation to Manual, and disable background refresh during the load. Even with these steps, exporting 12M rows to worksheets will still take some time, as the limitation is mostly on Excel’s side rather than Power Query.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
Do you need the table in worksheets, or would having pivot table based on full 12mln be sufficient? You can load it to the PowerPivot data model, potentially add custom columns (in DAX).
As for the performance - I'm not sure if MS Access has query folding, I wouldn't be surprised if it applied the filter in mashup engine (essentially loading 12mln rows to memory to then filter it - twelve times). I would try creating views in MS Access so that the filters for sure would happen in database, but I think PowerPivot solution is more elegant.
Hello,
Yes I need all the records that's in the MS Access to be in MS Excel for Audit purposes, as they're all GL transactions.
And auditors asked it to be in MS Excel as they're not used to MS Access (i.e. querying and/or filtering).
Thus the post asking what's the best way to make the "load to excel" faster
