Single Query runs in power query but will not load into power bi m engine error

[Simon_Newton]

hi all,

i am getting this Mengine error; microsoft mashupengine [expression error] we cannot convert the value 599 to type list

here is my code its calling less the company code redacted.

let
BaseUrl = "https://api-uk.vfp.viewpoint.com/vfp/",
Endpoint = "api/v2/enterprises/123456789101112/users",
Token = Auth_4Projects_Token,

Options = [
RelativePath = Endpoint,
Headers = [
Authorization = "Bearer " & Token,
Accept = "application/json",
#"User-Agent" = "PowerBI"
],
Timeout = #duration(0,0,10,0)
],

Get = () => Web.Contents(BaseUrl, Options),

T1 = try Get(),
R1 = if T1[HasError] then null else T1[Value],

R2 = if R1 = null then Function.InvokeAfter( () => Web.Contents(BaseUrl, Options), #duration(0,0,3,0)) else R1,
R3 = if R2 = null then Function.InvokeAfter( () => Web.Contents(BaseUrl, Options), #duration(0,0,6,0)) else R2,

Raw = if R3 = null then error "API timed out after 3 attempts." else R3,

JsonResponse = Json.Document(Raw),

Result =
if Value.Is(JsonResponse, type list) then
Table.FromRecords(JsonResponse)
else
error "Unexpected JSON format.",

BufferedResult = Table.Buffer(Result)
in
BufferedResult

this runs in query and calls a small table of results - with headers email - name - orgid - profilid- type  with 8 rows below it.

v confused why this is hanging. thanks in advance!

help m

[Simon_Newton]

thanks Ganesh  i now also see the limits of the desktop app. 

regards

simon

[v-ssriganesh]

Hello @Simon_Newton,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @Simon_Newton,
Thank you for posting your query in the Microsoft Fabric Community Forum.

Based on the details shared, this error occurs because the API response is not consistently returning a JSON list. In some cases, the API appears to return a different payload (for example, a numeric value such as 599, or an error/timeout response). When Power Query evaluates Json.Document(Raw), it expects a list of records, but instead receives a non-list value, which results in the error “We cannot convert the value 599 to type List.”

Although the query may work correctly in the preview, during refresh Power BI can execute the query multiple times. Under these conditions, the API may return throttling, timeout or error responses with a different JSON structure, which leads to the load failure.

To resolve this, we recommend updating the query to:

• Validate the HTTP response status before parsing the content,
• Handle both list-based and record-based JSON responses, and
• Call Table.FromRecords only when the response is confirmed to be a list.

For your reference: https://learn.microsoft.com/en-us/powerquery-m/json-document

Best regards,
Ganesh Singamshetty.