0

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?

Improve this question
5
  • Can you give an example of a few rows? Commented Jan 4, 2018 at 21:39
  • 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. Commented Jan 4, 2018 at 21:52
  • 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. Commented Jan 4, 2018 at 21:59
  • 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... Commented Jan 4, 2018 at 22:12
  • My approach would be to create a copy and search for the erroneous cells in table DataEntry3, by systematically removing parts of the data. Commented Jan 6, 2018 at 8:46

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This was mine, but it was hard to find. I tried to remove rows and columns in Power Query to identify the bad data, but the error showed up in every column, and about 400 rows above the bad data. Then I deleted columns in Excel and I was able to find the column quickly. It was in a hidden column too, making it even harder to spot. To find the bad rows, you can look in AutoFilter and the bad date format will show up as ####### at the top of the AutoFilter drop-down list.
1

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.