An annoying error when using Get & Transform (PowerQuery) in Excel is the overflow error as it is not always clear what it may be. The error says
- ‘[Expression.Error] An error occurred while accessing table XYZ because it contains overflow errors. Please fix the errors and try again’
The overflow error in Excel PowerQuery is when the ‘number’ Excel sees is too big to handle. So if you have numbers in trillions this could cause the problem. However, you are normally aware if you are dealing with such big numbers.
Overflow error in Excel PowerQuery and Dates
The most common reason is numbers that (for some reason) become formatted as dates. It is not clear why this happens sometimes, but it happens often enough that it should be considered. As shown below, column B in the table is clearly meant to be numbers but the last few are formatted as dates.
Dates in the Excel world are days from the 1 Jan 1900 and are currently in the 43 000 range. Normal Excel allows you to have a date of 31 Dec 9999. This converts into the number of 2 958 465.
If you have a number that is a bit bigger (bigger than 2 958 465) and it is accidently or somehow formatted as a date in Excel, when you pull it through to PowerQuery it treats it (correctly) as an impossible date. So in the above image row 8 and 9 would be acceptable, by row 10 will cause the overflow error.
The issue of normal numbers suddenly being formatted as a date often seems to happen lower down in your data, so when you look at the first page everything looks OK, but lower down you will see numbers formatted as dates. You just need to correct this and PowerQuery will handle it better.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.