Forum Discussion
Row limit for Excel power query with setting "connection only"
Hi Excel Forum,
I know the limit of rows in Excel is 1048576. Therefore a Power query, that loads to a worksheet, needs to be under this limit.
Is it possible to exceed the row limit of 1048576 in a power query that is a "Connection only" (Query 1)
Then have a new query (Query 2) that has it's source set to (Query 1) but has filters to limit the number of rows
Only Query 2 would be loaded to an Excel worksheet
I am pretty new to Excel Power Query, but i frequently transform data in multiples of 5 to 8 million rows,
Last week i was attempting to create a New Merged Query
A 9 million row query with a 5 million row query, in hindsight this was a poor decision, but it worked after waiting for 60+mins
in my little experience Power Query does not seem to have a upper limit, but strangely its default table output can only be to a Excel sheet, thus limiting it to 1 Mil as a table, But its "To a Connection" does not have a upper limit
Also there is a add in called Dax Studio, it lets you overcome the export to table limitation by saving your Query table output to a CSV file,
If you have millions of rows most probably you query SQL database. Or maybe OData endpoint. If something like this pay attention to query folding, that could increase the performance dramatically.
If to load query into the grid it's limiting b grid size, i.e. bit more than 1M of rows. If load to data model on practice it's limited only by your PC memory.
In general if you do nothing within Excel perhaps it's easier to use Power BI Desktop.
On practice it's limited only by computer resources. Performance could be quite low with such size. Think about query folding if it is applicable in your case.
