JSON Error: Int is too large or too small

[dgw_REA]

I am looking for help with a Power Query error on an existing report that has refreshed in PowerBI desktop perfectly fine until yestrday morning. The resulting error is as follows.

"JSON integer 4294967295 is too large or small for an Int32. Path '[25].MaxLength', line 1, position 9475."

Where do I look for the error based on the line and position given and is this in the Power Query Advanced Editor?
Is it soley limited to the 1 queries the error is referencing or could it be in one of the others and connected with lookup columns?
Should I be looking at changes within the data source?

Is this a known or new issue that others are seeing? I appreciate any help provided.

[jasonbub_seasi]

Hi dgw_REA,

That is really strange that you are also just now experiencing this issue, as I too just started to have the exact same problem.

Mine appears to be related to a "split" operation that I am doing on a comma-separated value in which I am splitting the data into 8 columns based on a comma.

This was working just fine 2 days ago and now I am getting this same error.

Error Message:
JSON integer 4294967295 is too large or small for an Int32. Path '[33].MaxLength', line 1, position 12296.

Stack Trace:
Newtonsoft.Json.JsonReaderException
   at Newtonsoft.Json.JsonTextReader.ParseReadNumber(ReadType readType, Char firstChar, Int32 initialPosition)
   at Newtonsoft.Json.JsonTextReader.ReadNumberValue(ReadType readType)

Any ideas would be very much appreciated!

Jason

[marcjb]

Hi 

This seems to be from an update.
The problem seems to be:
Data_type = longtext

All other field formats seem OK.

Either change field type or select field in power query and press 'detect data type'
This worked for me - unfortunately I have a very large DB with about 100 tables 

[v-xiaoyan-msft]

Hi @dgw_REA ,

We have seen shares from other users in the forum：

”Users can try Detect Data Types in Power Query Editor before loading data into Power BI Desktop.“

No other workaround for now.If there are other users who have a better way to solve this problem, you are welcome to leave this valuable answer in the post

Best Regards,
Community Support Team _ Caitlyn

[jbianco]

Hi everyone 

This issue was in the update to version 2.106.582.0 (June 2022)

to solve the problem

1) Enter power query.
2) On the queries tab, choose the table that presented the error.
3) find the column with the problem, for that the clue is at number 25 "Int32. Path '[25].MaxLength'". The error is in the twenty-fifth column, remembering that the first column is column zero.

4) in the transformation steps tab, choose the navigation step, so this transformation happens before the others

5) Click in the Transform->Detect Data Types

6) Save and Apply.

Repeat the process if the error appears in other columns.

For me the error happened in columns of type text. I didn't understand why Power bi is trying to convert them into a number, I don't use them as a number.
I think it happened because the column has a large number of null elements and it has decided to use number before receiving the first value, with the arrival of a text the system presents the error.

I hope this helps

[JohnDo]

The procedure @jbianco wrote is excellent. I have very little skill with Power BI and was able to resolve the situation easily with this advice.

1) Enter power query.
2) On the queries tab, choose the table that presented the error.
3) find the column with the problem, for that the clue is at number 25 "Int32. Path '[25].MaxLength'". The error is in the twenty-fifth column, remembering that the first column is column zero.

4) in the transformation steps tab, choose the navigation step, so this transformation happens before the others (I had to click Detect Problems to get this to show up at all then fangled things from there)

5) Click in the Transform->Detect Data Types

6) Save and Apply.

Repeat the process if the error appears in other columns.

[ConnieRW]

Thank you all! Last week I thought this issue was with a binary true/false field in my table. You helped me recognize that sure enough the problem originated with the long text fields. I too followed @jbianco's suggestion and am happy to report that table refreshing is working again. If I could upvote that post I would.

[aanyoti]

Thank you @jbianco !!

Your solution worked for me.

😁

[ZYSHAQ]

I had this problem today. The refresh worked perfectly yesterday to my hostmonster website MySQL DB. I did not have any new data added to the DB. Today I receive the same error:

Feedback Type:
Frown (Error)

Error Message:
JSON integer 4294967295 is too large or small for an Int32. Path '[4].MaxLength', line 1, position 1650.