Power BI Expression.Error: We cannot convert a value of type Record to type Type. Details: Value=[Re

[Fabi66]

I get this error in Power BI:

Expression.Error: We cannot convert a value of type Record to type Type.

Details:

    Value=[Record]

    Type=[Type]

When I try to use an existing column to query a new endpoint.  I converted the existing column to Int64.type and the endpoint documentation says the field is 

CompanyId - integer<int64> or null.

I believe it's the "AddAPIQuery" in the code below that's generating the error.  I've tried changing the type still receive the same error.  Any ideas?

let
    Source = Json.Document(Web.Contents("https://my.website.com/rest/api/pipeline?Query=arrangeddate%20ge%208%2F01%2F2023", [Headers=[Authorization="Bearer xxxMyTokenxxx", #"Content-Type"="application/json"]])),
    Results = Source[Results],
    
   #"Converted to Table" = Table.FromList(Results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"SendoutId", "JobId", "CandidateId", "CandidateName", "CompanyId", "AppointmentDate", "ArrangedDate", "InterviewType", "InterviewStatus", "BillRate", "PayRate", "Notes", "Feedback", "Guarantee", "NotesPublic", "UserName", "InterviewerIds", "InterviewerNames", "CurrentInterviewType", "CurrentInterviewStatus", "CurrentRollupStage", "VisibleOnWeb", "LastModified", "Rank", "Position"}, {"SendoutId", "JobId", "CandidateId", "CandidateName", "CompanyId", "AppointmentDate", "ArrangedDate", "InterviewType", "InterviewStatus", "BillRate", "PayRate", "Notes", "Feedback", "Guarantee", "NotesPublic", "UserName", "InterviewerIds", "InterviewerNames", "CurrentInterviewType", "CurrentInterviewStatus", "CurrentRollupStage", "VisibleOnWeb", "LastModified", "Rank", "Position"}),
    
   #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"CandidateId", Int64.Type}, {"CompanyId", Int64.Type}}),
    
   #"Expanded Position" = Table.ExpandRecordColumn(#"Changed Type", "Position", {"JobTitle"}, {"JobTitle"}),
    
   #"Removed Columns" = Table.RemoveColumns(#"Expanded Position",{"Rank", "VisibleOnWeb", "CurrentRollupStage", "InterviewerIds", "InterviewerNames", "NotesPublic", "Guarantee", "Feedback", "BillRate", "PayRate"}),
    
   #"AddAPIQuery" = Table.AddColumn(#"Removed Columns", "APIQuery", each "https://my.website.com/rest/api/candidates?Query=CandidateId%20eq%20Type64.From([CandidateId])%20&Custom=Source", [Headers=[Authorization="Bearer xxxMyTokenxxx", #"Content-Type"="application/json"]]),
   
    #" InvokeAPI" = Table.AddColumn(AddAPIQuery, "APIResponse", each Json.Document(Web.Contents([APIQuery])))
in
    #" InvokeAPI"

[Fabi66]

Hi Jenn,

I removed the "Type64.From" in the "APIQuery" column definition.  But I still get the same error.  

[jennratten]

This is a Table.AddColumn function, so the highlighted portion of your script refers to the argument in the syntax that is looking for the column type, but instead of the type, such as type text, Int64.Type, etc, it is being fed a record.  You should replace this with 'type text'.

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

[Fabi66]

I tried a couple of things but I guess I don't know how to do what you suggest.

I need a column creating the Rest API call (APIQuery) which has to have header information.

Then I'm using a second custom column to display the results of the API call 

#" InvokeAPI" = Table.AddColumn(AddAPIQuery, "APIResponse", each Json.Document(Web.Contents([APIQuery]))  

[jennratten · Accepted solution]

You could do it like this:

  _BaseUri = "https://my.website.com/rest/api/candidates",
  //_RelativeUrl = ,
  #"InvokeAPI" = Table.AddColumn(#"Removed Columns", "Results", each Json.Document(
    Web.Contents(
      _BaseUri, [
        //RelativePath = _RelativeUrl,
        Headers=["Bearer xxxMyTokenxxx", #"Content-Type"="application/json"],
        Query=[CandidateId=[CandidateId]]
      ]
    )
  )
)

[Fabi66]

Finally got it to work using 

#"Add APIQuery" = Table.AddColumn(#"Removed Columns", "APIQuery", each Json.Document(Web.Contents(
      "https://www.xxx/rest/api/candidates/" & Text.From([CandidateId]) & "/?Fields=LastName&Custom=Source", [Headers=[Authorization="Bearer ***Token***", #"Content-Type"="application/json"]]
  )
  )
  )

Thank you for your help!

[jennratten]

Hello - it looks like you have already converted your CompanyId to Int64.Type in the #"Changed Type" step so I don't think there would be a need to include an additional accommodation for type in the #"AddAPIQuery" step.  When you see the error you should see a 'Go to Error' button.  This should take you to the step containing the error and also show the actual error record.  Can you please try that and then post screen shots of what you see?  Thanks!