DataFormat.Error: We reached end of buffer Error

[MelanieRoever]

Hello everyone,

I need your help, please.
I'm trying to fetch data from a grapghql API, other requests are working fine with the exact same API but this special one here, I always get the error: DataFormat.Error: We reached end of buffer Error.
Clicking on: Show me the error leasds to the jsonResponse part but there is no format (owrking in the other requests, too).

let
url = "MY URL",
query = "{""query"": ""{ getReporting(onlyAktive: true, to: \\""2024-09-30\\"", from: \\""2024-09-29\\"", onlyOwnProjekte: false, onlyOwnTeam: false, onlySalesforecast: false) { projekte { id name kundenname projektNummer projektbeschreibung aeGeplant aeErfasst aeGesamt umsatzGeplant umsatzRealisiert umsatzGesamt } } }""}",

headers = [
#"Content-Type"="application/json",
#"Authorization"="Bearer ],

body = Text.ToBinary(query),

Source = Web.Contents(url, [
Headers=headers,
Content=body,
ManualStatusHandling={400, 401, 403, 404, 500} 
]),

jsonResponse = Json.Document(Source,65001),

data = jsonResponse[data],

tableData = Record.ToTable(data),

expandedTable = Table.ExpandRecordColumn(tableData, "Value", {
"projekte"
}, {
"projekte"
}),
expandedProjekte = Table.ExpandRecordColumn(expandedTable, "projekte", {
"id", "name", "kundenname", "projektNummer", "projektbeschreibung", "aeGeplant", "aeErfasst", "aeGesamt", "umsatzGeplant", "umsatzRealisiert", "umsatzGesamt"
}, {
"id", "name", "kundenname", "projektNummer", "projektbeschreibung", "aeGeplant", "aeErfasst", "aeGesamt", "umsatzGeplant", "umsatzRealisiert", "umsatzGesamt"
}),
#"Geänderter Typ" = Table.TransformColumnTypes(expandedProjekte,{{"id", type text}, {"projektNummer", type text}, {"aeGeplant", type number}, {"aeErfasst", type number}, {"aeGesamt", type number}, {"umsatzGeplant", type number}, {"umsatzRealisiert", type number}, {"umsatzGesamt", type number}})
in
#"Geänderter Typ"

 Thanks for your support.

Best,
Melanie

[v-tianyich-msft]

Hi @MelanieRoever ,

Try change max cache size in the option. It looks a memory issue.

Please feel free to correct me and provide more information if I have misunderstood you!

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MelanieRoever]

Hello @v-tianyich-msft ,

unfortunately that doesn't fix the issue.
I reduced the query even to one day and the original response has just 2.285 lines (49.23kb). 
That doesn't look like a size issue 😞 - any other idea?

Best,
Melanie 

[v-tianyich-msft]

Hi @MelanieRoever ,

Check whether the API interface return data format is the same as the code JSON format, please use the tool debugging.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MelanieRoever · Accepted solution]

I found the issue in my query and solved it - thanks for your support