Microsoft Excel Power Query Expression Error (overflow error)

0
votes

I couldn't find this issue elsewhere on stackoverflow, so here goes: I am loading a table (named DataEntry3, approx 10K rows and 30 columns) from the same workbook into Power Query, but it is throwing the following error message:

[Expression.Error] An error occurred while accessing table DataEntry3 because it contains overflow errors. Please fix the errors and try again.

I'm confused since this is a fairly routine operation. Any ideas as to what might be going on?

3
excelpowerbipowerquery
Can you give an example of a few rows? – Alexis Olson
I'm afraid I can't - this is confidential company data that my employer will not allow me to share. However, I found something interesting that I can't quite wrap my head around. If I load the content of the sheet into Power Query (Data --> New Query --> From File --> From Workbook), I don't get the Expression Error listed above. This approach works, but if I make changes to the original table I will have to save the file for the edits to be reflected in the table I'm loading into Power Query. I think i have a workable solution for now, just a little puzzled why I cant load the table directly. – Roald Schuring
I don't want to see the data in the rows, just what type of data it is. It's possible that Power Query is automatically choosing the wrong data types for certain columns and that's causing an overflow. – Alexis Olson
The data is a mix of numeric and text variables. Power Query is using the Data Type 'Any' for all columns when loading in the data. The error I described already appears before Power Query tries to change the data type of each column to text/whole number/etc... – Roald Schuring
My approach would be to create a copy and search for the erroneous cells in table DataEntry3, by systematically removing parts of the data. – MarcelBeug

3 Answers

2
votes

Do you have any large numbers in your dataset? This can occur if a large number is mistakenly formatted as a date in Excel. I would check if giving a stronger type (Text or Number) to the columns resolves this issue.

1
votes

Coming late but might help someone else.

Had this same problem and discovered that the Source location for the file was wrong (internal network changes and drive names changed)

Fixing this fixed the problem.

0
votes

In my case it was a formatting issue. I used format painter for the new lines that were added to my source table and that fixed the problem.