Forum Discussion
Fast Load "export" to Excel
Hello PQ Gurus,
I have a MS Access file from IT that has 12 million rows (for simplicity).
Due to the limit of rows in Excel, I'm trying to export these 12 million rows into 12 sheets (1 million rows per sheet).
I use Power Query to connect to MS Access (Data \ Get Data \ From Database \ From Microsoft Access Database)
When I'm connected, I have 1 'query' which I call it 'tblMSAccess'
let
Source = Access.Database(File.Contents("C:\Users\... ... Name of MS Access.mdb"), [CreateNavigationProperties=true]),
_Query = Source{[Schema="",Item="Query"]}[Data]
in
_Query
In which then, I created 12 additional queries referencing 'tblMSAccess', where the only difference between those queries are the [period]=202501.
let
Source = tblMSAccess,
#"Filtered Rows" = Table.Buffer(Table.SelectRows(Source, each ([period] = 202501)))
in
#"Filtered Rows"
In PQ, these are rather fast preview/refresh. However, when I export/load these 12 queries out to Excel, it's very slow.
I had tried Table.Buffer (as you can see above) and it doesn't help either (from my perspective, it seems to be longer).
My question is, how can I make the "load to excel" faster (in a sense, I don't have to wait for 5 minutes)?
Thank you
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.
12 Replies
Hi,
die Frage ist, benötigst Du wirklich alle Zeilen, aller Spalten aller Perioden? Vermutlich nicht, denn die Du könntest Du ja auch direkt in der DB ansehen. Deshalb wäre es empfehlenswert, nur die Spalten und Werte, evtl. über Parameter, herauszufiltern, die Du tatsächlich benötigst, so dass (im günstigsten Fall) am Ende nur eine Abfrage (statt 13) als Ergebnis steht, die Du dann auch noch aus der Verbindung heraus (also ohne zwischenladen in eine Tabelle) in PowerPivot weiter bearbeiten kannst.
After translating, the short answer is that I need all the records that's in MS Access to be in MS Excel for audit purposes.
Thus the question asking how to make the load to excel be fast.
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
As mentioned, if you abosloutely need to get this into one xlsx instead of 12+ (because the auditor is being lame), then, as previously mentioned, your best bet is to load into the power pivot data model, not directly into any sheets. The model can handle 12m+ rows all at once.
From there, you can do targeted extracts from the model via what are referred to as linked tables in this article.
E.g. you can add a table and then use the Edit DAX function with something like:
EVALUATE FILTER( 'Your 12m table in model', 'Your 12m table in model'[ID] >= 1 && 'Your 12m table in model'[ID] < 1000000 )In powerquery the column profiling is by default based on the top 1000 rows .
As the preview/refresh in power query is limited to that number of rows, it will response faster than the time it takes to load the data.
The buffer.table will not accelerate the response in this case where you have big tables: Table Buffer is not a magical function that will make every code run faster, it is beneficial in situations where you have small tables that you have to reiterate over and over again, the way it works is: it loads the specified table into the memory so it can be accessed there through the execution.
https://learn.microsoft.com/en-us/powerquery-m/table-buffer:I hope this helps. if so please mark it as a solution. Kudos are welcome.
Please take into account the previous responses of MarkLaf , Stachu , Stachu and ralf_anton where it was suggested not to load the 12 million records into Excel worksheets. It is not efficient, and Excel is not designed for handling that volume of data. It is better to use Power BI or another tool that supports large-scale data processing.
In any case, if it must be done in Excel, a much better option is to load the data only into the data model (without physically loading the data into worksheets) and extract aggregated values as needed using PivotTables.
I thank everyone for their inputs.
I am completely agree that it is definitely not the best option to load all the records that is in MS Access to MS Excel into 12 different sheets in the same workbook at 1M each.
However, the point that I'm trying to learn here is not about best practise, but to understand if there's a way to make the load to excel faster.
As in this ask, there's no filtering etc (except filter to each period in order to load to a sheet), but just an export of records to excel. Due to the slowness of "exporting", am thinking if there are ways to make it faster.
