Error - The Query Returned More Data Than Will Fit On A Worksheet

Don't try to process so many rows of data. If the resulting query exceeds the maximum rows in Excel, you will be stopped in this way.

I'll create data from this range with power query. However I can't take data onto Excel because of this error.

So I don't have any table yet.



I don't understand where I right click.

I randomly created an empty table and right click it but there is no properties option. ??

No - not a new table. Click anywhere in one of the tables created by your queries (connections) and the properties option becomes available.

@zanshin777;

See the picture below....

As you see, cell C6 is selected in the table range and the red arrow shows the "Properties" menu button.

Change the options related with inserting rows of data and see the effects, if it helps...

I tried what you suggested. I get the same error.

On the pop-up menu It says;


"To continue and display as much of the data as will fit, click OK"


1) I clicked OK but I guess this time returned data does NOT include all of the data. Right?

2) If so, Is there a way to split and transfer the output data to different workbooks?

3) Is there any other advanced software which I can manage this whole process?

4) There is also a thread here;

https://www.excelforum.com/excel-gen...readsheet.html

Does it work?

I can't open the link. It says "Your request has been blocked. "

How can I increase the capacity of Excel Workbook?

I'm trying to calculate possibilities of some cases with power query and power query gives this error.

If the calculations can be done in a pivot table, possibly using DAX formulas in power query, then you might consider loading the query into a data model.
The video linked below may be of some help also.
https://www.youtube.com/watch?v=Nmn6qUJvWWY

I am not sure that I understand what you are attempting to accomplish.
It appears that each column is a table (R1...R7) and if the table is too large to fit on a worksheet I assume that means that it has more than 1048576 rows.
When work on the table is completed in power query, instead of choosing "Close and Load" choose "Close and Load to..." then choose both connection only and add to data model.
If the above suggestion isn't helpful then perhaps uploading a file that includes an example of what has been done in power query will help someone to understand.
Let us know if you have any questions.

I'm trying to calculate all the possiblities with power query;

A1-B1-C1-D1-E1-F1-G1
A2-B1-C1-D1-E1-F1-G1
A3-B1-C1-D1-E1-F1-G1
A1-B2-C1-D1-E1-F1-G1
A1-B3-C1-D1-E1-F1-G1
...

1) I create named ranges along the columns R1-R7

2) Go to "Data - Get External Data - From Other Sources - From Microsoft Query - New Data Source - OK"

3) Move the columns R1-R7 to the right side.

4) Go to "File - Return Data to Microsoft Excel"

However I can not import that data calculated with power query because data is so big.

So how can I do that?