API Error in Power Query

[ChrisPBIUser]

Morning,

I have several lean reports connecting to one dataset in services and the last successful refresh was 9pm last night (30th Mar 22).  However, the first refresh this morning at 6am (31st Mar) I getting the following error :

Expression.Error: We cannot convert a value of type Record to type List.
Details:
Value=
Type=[Type]

In service I'm getting this error

Expression.Error: We cannot convert a value of type <pii>Record</pii> to type <pii>List</pii>.. Value = <pii>[]</pii>. ;We cannot convert a value of type Record to type List.. The exception was raised by the IDbCommand interface. 

The M Code for the data query is  :

let url="https://url",

body="{""APIKey"": ""APIKEY"",
""Action"": ""GetQueryResultByQueryName"",""QueryName"":""File Name""}",

Source = Json.Document(Web.Contents(url,[

Headers = [ #"Content-Type"="application/json"],

Content = Text.ToBinary(body)

]

)),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{3}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Employee Id", "First Name", "Last Name", "Known As", "Gender", "Work Email", "Department", "Job Role", "Job Role Change Date", "Analysis Code 1", "Analysis Code 2", "Age", "Date of Birth", "Start Date", "Reports To", "Manager Email", "FTE", "Employee Status", "Reason for Leaving", "Final Day of Employment"}, {"Employee Id", "First Name", "Last Name", "Known As", "Gender", "Work Email", "Department", "Job Role", "Job Role Change Date", "Analysis Code 1", "Analysis Code 2", "Age", "Date of Birth", "Start Date", "Reports To", "Manager Email", "FTE", "Employee Status", "Reason for Leaving", "Final Day of Employment"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Final Day of Employment", type date}, {"Start Date", type date}, {"Date of Birth", type date}, {"Job Role Change Date", type date}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Manager Email", Text.Proper, type text}, {"Work Email", Text.Proper, type text}})
in
#"Capitalized Each Word"

Is there any help/solution that anyone could provide.  I've extracted the raw data manually into a csv and all seems correct.

[Anonymous]

Hi @ChrisPBIUser ,

Such error is coming from Power Query when you are loading data, and somewhere either in your M code or source data, you are getting a Record type of data and are then trying to handle it like a List data, thus the error.

Please go to Power Query window, and navigate to the error steps,

About Convert  Record type/ List Type, please kindly refer to:

Record.FromList - PowerQuery M | Microsoft Docs

Record.ToList - PowerQuery M | Microsoft Docs

A similar thread:

https://stackoverflow.com/questions/63614882/power-query-expression-error-a-value-of-type-record-can...

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

[ChrisPBIUser]

Cheers for your reply.  It transpired that the source system was down which was causing the load error but I'll take on board your advise should it happen again.

[ChrisPBIUser · Accepted solution]

Since posting the next scheduled refreshes have been OK with the api data source not being changed.