Error Message about too much information

[Mr_Phil]

Hi, I am really new to power query and haven't yet mastered the art of not getting errors LOL. I have not encountered this until today. The queries in question all ran fine or so it appeared. They have never caused an error before either.

 

[Joe4]

An Excel worksheet can only have 1,048,576 rows in it, maximum.
I think that message is telling you that your query would return more than that number of records, so it cannot return all the results to a single worksheet.

 

[Mr_Phil]

Ok. I can look into that.

An Excel worksheet can only have 1,048,576 rows in it, maximum.
I think that message is telling you that your query importswould return more than that number of records, so it cannot return all the results to a single worksheet.

Hmmm. Got my head scratching finger out more. The biggest of the source files is barely 50k row’s. We recently put the source into MS Onedrive. I wonder if that is a possible source of the issue. I note it isn’t local as another person gets it.

 

[GraH]

Try to filter out any null values in a column where you do not expect them.
In the source file do a CTRL+End. If you jump to a cell way beyond the filled range that can be the cause. Delete all unused rows and columns of that file. That might solve it.

 

[Joe4]

Ok. I can look into that.

Hmmm. Got my head scratching finger out more. The biggest of the source files is barely 50k row’s. We recently put the source into MS Onedrive. I wonder if that is a possible source of the issue. I note it isn’t local as another person gets it.

Depending on how you write your queries, you could have a one-to-many relationship, in which your query could return many more rows than exist in any single table involved in the query. So you may still be exceeding the upper row limit of Excel.

For example, let's say that you have two tables:
- TableA: has 10 rows
- TableB: has 25 rows

If you do a query between these two tables without a join, your query will be a Cartesian product (i.e. every record in TableA will have a matching query result with every record in TableB).
That would return 250 rows (10 x 25)!

Even tables that have valid joins between the two tables can return more rows that exist in the largest underlying table.