Number must be either non-negative and less than or equal to Int32.MaxValue or -1

[Barthel]

Hello Community,

We are in the process of retrieving API data. The data consists of invoices. The amount of data you can retrieve per API call is limited. That's why we fetch the API in partitions. We do this on the basis of the invoice id. For every invoice id we perform an API call. 

The basis of our table with which we want to retrieve the API data, consists of all unique invoice ids. The format of the id is text.

id

304178982562039733

290575968940066623

...

Then we execute the API call as a calculated column. The API link is dynamic based on the id value of the current row. The link also contains our administration code and the key for the header. These are fixed parameters in the file.

#"Added Custom" = Table.AddColumn(Base, "API", each Json.Document(Web.Contents(Text.Combine({"https://moneybird.com/api/v2/", AdministratieCode, "/documents/purchase_invoices/", [id], ".json"}), [Headers=[Authorization= APIKey]])))

Then we only expand the retrieved API data as columns and change the type of the columns. No other transformations are done whatsoever. When loading the data we get the following error:

Anyone have any idea what's going wrong here? 

[hcbrecht]

Hi,

I am getting the same error and I have no idea where to search. It comes up in two queries, one is just a join ot two queries - no error there and one ist just a group-by of another query (which also seems to have no error).

Any idea anyone? 

[Barthel]

@BA_Pete @AlexisOlson 

Thanks for your responses.

As soon as I add the calculated column the preview works.

Until I do a full refresh: either by loading the data or by clicking the refresh button.

So this error occurs even before I expand the columns.

[BA_Pete]

Your [id] column is untyped. Try setting the data type of this column to text before the 'Added Custom' step.

Pete

[Barthel]

Hey @BA_Pete 

Thanks again for your response. Unfortunately, it doesn't work even after changing the type to text.

[BA_Pete]

Hi @Barthel ,

It looks like when the call data is passed back, the Invoice ID is coming back into an Int32 data type field, for which your Invoice ID as an integer is way too big.

Do you have an opportunity within your code to apply a type change to Int64 before the error throws? This would fix it.

Pete

[AlexisOlson]

This seems plausible.

@Barthel what is the first step that returns this error? For example, #"Added Custom", #"Expanded Column", or #"Changed Type".