Number is out of range of a 32 bit integer value

[stizein]

The error listed in the subject line is received when I attempt a data refresh within the desktop version. The online version also fails to refresh data. The following is the advanced query.

I believe the issue stems from the "Filtered Rows" custom field. I'm not sure what the Number.From.. does as the code was developed by a 3rd party. How do I identify the record(s) causing the error to refresh the data? Thanks for your help!

[Greg_Deckler]

I would expect that if a single row was causing an error that you would get an error for that row. Sounds like the entire query errors out? I would start at the top of your query and change any whole value columns to decimal columns and that might get you around this issue. Difficult to read your code pasted without line breaks.

[stizein]

Other than Let and Source... here is the formatted query.  

The refresh worked fine up to a week or so ago which makes me think it's a portion of the data.  If I remove either the effective date or the expirationdate section from the 2nd line, the refresh will complete.  It's the combination of and not understanding what the 2nd line is doing that is throwing the refresh (and myself) off.

[Greg_Deckler]

Hmm, invoking @ImkeF, she is much better with the M...

[ImkeF · Accepted solution]

Hi @stizein,

this error is probably due to some value(s) in the column "EffectiveDate" or "ExpirationDate" not being a valid date format. If thay are they are not specifically formatted as dates already, doing so might help. If not, errors will be returned in the problematic rows and you could delete rows with errors, if that's appropriate for your use case.

The 2nd step starts building a date-range by creating a continuous list of numbers from the date. If one of the start- or end-date-conversions returns a too large number, this error-message will return. 

[stizein]

Thank you!  

I added a step before the number.from to cast the row as a date field instead of date/time.  This seemed to resolve the issue.