DataFormat.Error: We have reached the end of the buffer IDbCommand (BI Service only not Desktop)

[Mal_Wallasey]

This is the example Power Query which gets data from SysAid (IT Helpdesk System)

let

Source = Table.FromColumns({Lines.FromBinary(Web.Contents("Paste the URL from POWER BI Extract addon", [Headers=[clientid="Clientid from POWER BI Extract addon", accountid="Your SysAid account id", authorization="The Token from Power BI Extract addon"]]))}),

#"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),

#"JSON to List" = #"Parsed JSON"{0}[Column1],

#"List To SysAid Table List" = Table.FromRecords({#"JSON to List"}),

#"Expanded SysAid" = Table.ExpandListColumn(#"List To SysAid Table List", "SysAid"),

#"Extract Headers" = Record.FieldNames(#"Expanded SysAid"[SysAid]{0}),

#"Merge Data To Table" = Table.ExpandRecordColumn(#"Expanded SysAid", "SysAid", #"Extract Headers")

in

#"Merge Data To Table"

 and it works fine in Power BI Desktop (after certain values replaced of course).

Credentials are correctly set for it in service

However when I try to refresh the dataset for it in BI service I get the following:

Data source error: [ValidateMarkupTags][ccon]DataFormat.Error: [ccon]We reached the end of the buffer.[/ccon]. [/ccon]. The exception was raised by the IDbCommand interface. Table: SysAid.
Cluster URI: WABI-UK-SOUTH-redirect.analysis.windows.net
Activity ID: 50cad8e1-3a19-4322-a5ca-2341a13eafd5
Request ID: 761eaf09-26e9-cb85-0735-61250b644148
Time: 2023-11-14 14:37:30Z

I've even tried a test PBIX file with the query and all but 2 columns removed and still get the same error.

[GilbertQ · Accepted solution]

Hi @Mal_Wallasey 

I think this can happen sometimes when the API does not complete successfully.

One workaround is if you have got an On-Premise gateway you could create a data source in the Gateway and then connect to it that way and see if that works?

[Mal_Wallasey]

Thanks. While I don't personally have access to that I'm sure a colleague does.