Parse Issue - "DataFormat.Error" We reached the end of the buffer.

[non23]

Hey guys,

I'm getting "DataFormat.Error" We reached the end of the buffer. error when I parse the below column.

[{"xxx":"UNA","xxx":"21","xxx":"UNA","xxx":"aaa","xxx":"1","xxx":"2","xxx":"12","WHATIWANTTOGET":"12345","XXX":"1234","XXX":"UNA","XXXX":"UNA"}]

Any tips on how to fix it?

[123abc · Accepted solution]

The error you're encountering typically occurs when Power BI encounters unexpected data formatting while trying to parse a column. The column you're trying to parse seems to contain JSON-like structures, but the formatting may be inconsistent or non-standard.

Here are a few steps you can try to fix this issue in Power BI:

1. Ensure Correct JSON Structure

• It seems like the JSON structure in the column is not fully correct. Specifically, the keys are all the same (xxx). Ensure that your JSON is properly formatted. Power BI may struggle to parse the column if the structure is off.

2. Convert Text to JSON

• If the column is being treated as plain text, you need to convert it into a proper JSON format that Power BI can read. You can do this using Power Query's built-in functions.

In Power Query, use the following steps:

• Select the column with the JSON data.
• Go to the Transform tab.
• Click Parse > JSON. This will parse the data into a structured table format.

3. Handle Buffer Overrun

• The DataFormat.Error may indicate that Power BI is hitting a limit while processing the buffer. To work around this, you can:
  • Split your column into smaller chunks.
  • Remove unnecessary fields from the JSON if possible, keeping only "WHATIWANTTOGET" or the fields you need.
  • Clean your data by removing any invalid or corrupted records before importing into Power BI.

4. Use Custom Column and JSON Functions

If the above solutions don't work, create a custom column to extract the part of the JSON you want (i.e., "WHATIWANTTOGET":"12345") and manually process it. You can do this by using Text.Middle, Text.BeforeDelimiter, or similar Power Query functions to isolate the specific value.

Example of extracting "WHATIWANTTOGET":

Text.Middle([YourColumn], Text.PositionOf([YourColumn], "WHATIWANTTOGET") + 17, 5)

5. Check for Encoding Issues

The error might also stem from encoding issues. Ensure the file or source you're importing is using a supported encoding format like UTF-8.

By trying these steps, you should be able to resolve the issue and successfully parse the JSON column.