DataFormat.Error: We reached the end of the buffer.

[Anonymous]

Hi,

So I am puzzled by this problem, and I am now at my wits' end.

I have a PBI Desktop report with tables from an on-prem SQL Server and an Azure SQL database using import. I tried to add two more tables from the same Azure database. Both tables have a column with a JSON object in it. The root table is the same in Azure SQL, but I get two different selections of it in Power BI. I go through these steps with each table:

Everything works fine in query editor, but as soon as I try to apply the changes, I get an error on one of the tables. The error reads: DataFormat.Error: We reached the end of the buffer. 

What I have tried:

- Loading only one of the new tables at a time. Only one of them fails. 

- I have the newest version of Power BI Desktop.

- Restarting PBI Desktop and the server I remoting to. 

- Checking resource usage on the Azure SQL DB. The queries are not consuming a lot of resources. 

Any suggestions are welcome!

[HarleyAtWork]

I had this issue when there was a strange Unicode character in one of the JSON files.

Removing the character resolved the issue.

[rockymtnrajah]

I was able to isolate this issue to the line that extracts json. 

If Json.Document get 0 bytes, it throws the "DataFormat.Error: We reached the end of the buffer".

To test this:

let
     Source = Json.Document("") //will throw DataFormat.Error: We reached the end of the buffer
in
     Source

[SofBL]

this is exactly what happened to me when I tried to import multiple json files where one of these was empty. Its maby to quick but I solved it by adding an extra step by removing errors before expanding the json as a table

[v-jiascu-msft]

Hi @Anonymous,

Which version of Desktop did you use? The 32-bit or 64-bit? I think this issue could be temporary. Maybe we can use the 64-bit Desktop to overcome this issue. Some references are as follows. 

1. https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/power-query-getting-dataformaterror-message-when/fe1fce88-2247-4522-a7d3-d387635a8882

2. https://community.powerbi.com/t5/Desktop/DataFormat-Error-We-reached-the-end-of-the-buffer/td-p/296097

Best Regards,

Dale

[Anonymous · Accepted solution]

Hi,

So, apparently just waiting and trying again the day after solved the problem. A bit frustrating since I used a couple of hours trying to troubleshoot. 

Anyways, now it's working!

[Anonymous]

I'm not 100% sure but it seems in my case R script parsing empty (not null) text cells into a new column produced this error as well.