Forum Discussion

ChrisPBIUser's avatar
ChrisPBIUser
Helper III
4 years ago
Solved

API Error in Power Query

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.

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

3 Replies