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

[zdice]

Hello,

I am at a 2 week loss trying to resolve this issue.  I have the following queries:
# RaasLogin

let
  dataServiceURL = "https://redacted/services/BIDataService",

  username = "redacted",
  password = "redacted",
  clientKey = "redacted",
  accessKey = "redacted",

  soapXMLlogin = Text.Combine({
    "<s:Envelope xmlns:s=""http://www.w3.org/2003/05/soap-envelope"" xmlns:a=""http://www.w3.org/2005/08/addressing"">
      <s:Header>
        <a:Action s:mustUnderstand=""1"">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/LogOn</a:Action>
        <a:MessageID>urn:uuid:7e5d2b3f-00cf-4171-b568-af831c49b747</a:MessageID>
        <a:ReplyTo>
          <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
        </a:ReplyTo>
        <a:To s:mustUnderstand=""1"">", dataServiceURL, "</a:To>
      </s:Header>
      <s:Body>
        <LogOn xmlns=""http://www.ultipro.com/dataservices/bidata/2"">
          <logOnRequest xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"">
            <UserName>", username, "</UserName>
            <Password>", password, "</Password>
            <ClientAccessKey>", clientKey, "</ClientAccessKey>
            <UserAccessKey>", accessKey, "</UserAccessKey>
          </logOnRequest>
        </LogOn>
      </s:Body>
    </s:Envelope>"}),
  soapXMLloginBinary = Binary.Buffer(Text.ToBinary(soapXMLlogin, TextEncoding.Utf8)),

  loginRequest = Web.Contents(dataServiceURL, [
      Headers=[#"Content-Type"="application/soap+xml; charset=utf-8", #"Accept"="application/xml"], 
      Content=soapXMLloginBinary
    ]),
  xmlText = Text.FromBinary(loginRequest, TextEncoding.Utf8),
  raasServiceId = Text.BetweenDelimiters(xmlText, "<ServiceId>", "</ServiceId>"),
  raasToken = Text.BetweenDelimiters(xmlText, "<Token>", "</Token>"),
  raasInstanceKey = Text.BetweenDelimiters(xmlText, "<InstanceKey>", "</InstanceKey"),
  loginRecord = Record.FromList({raasServiceId, raasToken, raasInstanceKey}, {"ServiceID", "Token", "InstanceKey"})
in
  loginRecord

 # RaasExecute

(REPORTID as text, raasServiceId as text, raasToken as text, raasInstanceKey as text) => let
  dataServiceURL = "https://redacted/services/BIDataService",
  clientKey = "redacted",

  soapXMLexecute = Text.Combine({
    "<s:Envelope xmlns:s=""http://www.w3.org/2003/05/soap-envelope"" xmlns:a=""http://www.w3.org/2005/08/addressing"">
      <s:Header>
        <a:Action s:mustUnderstand=""1"">http://www.ultipro.com/dataservices/bidata/2/IBIDataService/ExecuteReport</a:Action>
        <a:MessageID>urn:uuid:f42623fb-2d80-4860-baf2-18b9d4174cfd</a:MessageID>
        <a:ReplyTo>
          <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
        </a:ReplyTo>
        <a:To s:mustUnderstand=""1"">", dataServiceURL, "</a:To>
      </s:Header>
      <s:Body>
        <ExecuteReport xmlns=""http://www.ultipro.com/dataservices/bidata/2"">
          <request xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"">
            <ReportPath>", REPORTID, "</ReportPath>
            <ReportParameters/>
          </request>
          <context xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"">
            <ServiceId>", raasServiceId, "</ServiceId>
            <ClientAccessKey>", clientKey, "</ClientAccessKey>
            <Token>", raasToken, "</Token>
            <Status>Ok</Status>
            <StatusMessage i:nil=""true""/>
            <InstanceKey>", raasInstanceKey, "</InstanceKey>
          </context>
        </ExecuteReport>
      </s:Body>
    </s:Envelope>"}),
  soapXMLexecuteBinary = Binary.Buffer(Text.ToBinary(soapXMLexecute, TextEncoding.Utf8)),

  executeRequest = Web.Contents(dataServiceURL, [
    Headers=[#"Content-Type"="application/soap+xml; charset=utf-8", #"Accept"="application/xml"], 
    Content=soapXMLexecuteBinary,
    ManualStatusHandling = {200, 204}
  ]), 
  xmlText = Text.FromBinary(executeRequest, TextEncoding.Utf8),
  reportKey = Text.BetweenDelimiters(xmlText, "<ReportKey>", "</ReportKey")
in
  reportKey

Which I then sythesize in a query that provides the specific REPORTID for the above query like so

let
  loginData = RaasLogin,
  executeQuery = RaasExecute("special report string goes here",
    Text.From(Record.Field(loginData, "ServiceID")),
    Text.From(Record.Field(loginData, "Token")),
    Text.From(Record.Field(loginData, "InstanceKey")))
in
  executeQuery

My issue is that in the function, if I return `executeRequest` the dataflow gen 2 will refresh without error.  However, if I try ANY operation on that binary response, I get the following error

Query: Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: We cannot convert a value of type Table to type Record. Details: Reason = Expression.Error;ErrorCode = 10276;Value = #table({"Name", "Value"}, {});Microsoft.Data.Mashup.Error.Context = User (Request ID: redacted).

I have tried `Xml.Document`, `Xml.Tables`, `Binary.Buffer`, calling `Text.FromBinary` in the same line as the web call, and nothing gives me a different error than this.  

What's more annoying is that the query works perfectly in the editor.  While I'm writing the query and playing with parameters, the queries work perfectly and give me the results i expect.  It's only when i publish the dataflow gen 2 that it fails on refresh, which of course means i can't use the data in any of my downstream consumers or copy the data to a lakehouse, which is my ultimate goal.

Any help in this regard is greatly appreciated.

[zdice]

I was able to resolve the issue by reconsolidating my web calls into a single query instead of a query and a function.  A combination of not parsing the web results to a table and keeping them all in one call did the trick.  

I thank you for the insight on the privacy level handling that DFGen2 enforces.  The power query prompted me to disable the privacy levels when the issue first emerged and I had no idea that would not persist when uploaded to fabric, so that insight was invaluable.  thank you again.

[v-shamiliv · Accepted solution]

Hi @zdice 
Thank you for reaching out microsoft fabric community forum.
The "Mashup Exception Expression Error" in Power BI occurs when Power Query is unable to process an expression correctly. Specifically, the error message "We cannot convert a value of type Table to type Record" means that Power Query is expecting a Record, but it received a Table instead.

• Dataflows Gen 2 enforces stricter privacy levels and credential handling than Power Query in Power BI Desktop.Ensure your data source is set to Organizational or Public in Power BI Service → Settings → Data Source Credentials.
• Since you’re logging in and obtaining a token, check if the API session expires before the second query (RaasExcute).Log the token response (loginData) in a separate table to verify if the service returns an invalid session.
  

  If the issue persists, we recommend raising a support ticket for further assistance. To create a support ticket for Fabric and Power BI, please refer to the steps outlined in the following guide:

  How to create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

  
  If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community.

  Thank you.
  
  

[zdice]

I was definitely using disabled privacy settings in the editor and organizational privacy settings on the connections.  I will try to use public privacy settings on the connections and keep the privacy setting enforcement in my editor UI.  When I have a working solution, I will share here.  Also, the token has a 15minute expiration from the vendor's API documentation, so I shouldn't have an issue there with the token timing out.