Forum Discussion

Fabi66's avatar
Fabi66
Regular Visitor
3 years ago
Solved

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

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"
Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    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]]
      ]
    )
  )
)

     

6 Replies

  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    3 years ago

    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!

  • Fabi66's avatar
    Fabi66
    Regular Visitor
    3 years ago

    Hi Jenn,

     

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

     

     

    • jennratten's avatar
      jennratten
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        Fabi66
        Regular Visitor
        3 years ago

        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]))