Query Error

[Anonymous]

Hi All,

Any idea how to fix the error? I had this custom column for calculating FY working for some time, and now it stopped working. It gives out the error that "We couldn't convert to Number" in the final step. Any help would be appreciated.

Thank you!

[Jameswalter]

The error maybe data related (a transformation failed, a calculation overflowed, etc), or can be data size related (server gave up returning data, out of memory, driver failed, quota reach, etc)

Regards,
J Wick

[Anonymous]

It worked! looks like there was bad data coming in.

Thank you!

[edhans · Accepted solution]

There is data that cannot be converted to a number. "abc" would return that error. You have probably two options.

1. Fix the data so there is only values that can be converted to numbers.
2. use try/otherwise.

so try this:

if (try Number.FromText([Field]) otherwise 0) > 9 then.....

If [Field] has "100" in it, then it will evaluate to true, it is > 9. If it has "abc" it will return 0, and false. So alter based on your logic needs.

If you want to see why there are values that cannot be converted, create a copy of this query and change field to a number type, then filter on the home ribbon "KEEP ROWS, KEEP ERRORS" and it will show you the records that have the bad data.