Use column record in M Query / Power Query

[geniusbi222]

I've a short code to make a SOAP request to fetch data into Power BI. The problem is that I need to give a Session_id for the requests:

let
SourceURL = "HTTPS://SOAP.E-BOEKHOUDEN.NL/SOAP.ASMX?WSDL", //host provides this address. Url ends often with "wsdl"

options = [ #"Authorization" ="Basic USER:PASS=", //User:pass decoded with SOAP UI
            #"Accept-Encoding"= "gzip,deflate",
           // SOAPAction="", 
            #"Content-Type"="text/xml;charset=UTF-8",
            #"Connection"="Keep-Alive"
          ],
WebContent = Web.Contents(SourceURL, 
// Content options in Web.Contents() requires you to authenticate anonymously ! 
    [Content=Text.ToBinary("
    
    <soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns:soap1=""http://www.e-boekhouden.nl/soap"">
   <soap:Header/>
   <soap:Body>
      <soap1:OpenSession>
         <!--Optional:-->
         <soap1:Username>***</soap1:Username>
         <!--Optional:-->
         <soap1:SecurityCode1>***</soap1:SecurityCode1>
         <!--Optional:-->
         <soap1:SecurityCode2>***</soap1:SecurityCode2>
         <!--Optional:-->
         <soap1:Source></soap1:Source>
      </soap1:OpenSession>
   </soap:Body>
</soap:Envelope>

"), 
Headers=options]) ,
XmlContent = Xml.Tables(WebContent)
in
 XmlContent

Now this code is to get the Session_id. To get the other data I need to give the session_id instead of SecurityCode2 in a similar request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap="http://www.e-boekhouden.nl/soap">
   <soapenv:Header/>
   <soapenv:Body>
      <soap:GetMutaties>
         <!--Optional:-->
         <soap:SessionID>***</soap:SessionID>
         <!--Optional:-->
         <soap:SecurityCode1>***</soap:SecurityCode1>
         <!--Optional:-->
         <soap:cFilter>
            
         </soap:cFilter>
      </soap:GetMutaties>
   </soapenv:Body>
</soapenv:Envelope>

Can I use a column value as parameter/variable that I can use in the body?

[rodneyschaap]

Thanks for the information. It is related to a privacy setting in powerbi - Privacy level settings. This error is fixed.

Now I have the challenge of getting the data from a nested tables combining to one table 🙂 

[jbwtp · Accepted solution]

Hi @geniusbi222,

This extracts the SessionID from your "OriginalQuery" just change the OriginalQuery reference to your query's name:

let
    SessionID = OriginalQuery{0}[Table]{0}[Table]{0}[Table]{0}[Table]{0}[SessionID]
in
    SessionID

You can use it further in your following requests to the datasource.

Cheers,

John

[Kutsysteem]

Hi 

I have a simular issue. Power Bi gives this error: Formula.Firewall: Query 'Getmutaties' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Here is my code:

let
Url = "https://soap.e-boekhouden.nl/soap.asmx" ,
SessionIDpass = Opensession{0}[Table]{0}[Table]{0}[Table]{0}[Table]{0}[SessionID],

// SOAPEnvelope with correct XML-syntax and parameters
SOAPEnvelope =

"
<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">
<soap:Body>
<GetMutaties xmlns=""http://www.e-boekhouden.nl/soap"">
<SessionID>" & SessionIDpass & "</SessionID>
<SecurityCode2>xxxxxx</SecurityCode2>
<cFilter>
<MutatieNrVan>1</MutatieNrVan>
<MutatieNrTm>1000</MutatieNrTm>
<DatumVan>01/01/2023</DatumVan>
<DatumTm>01/01/2024</DatumTm>
</cFilter>
</GetMutaties>
</soap:Body>
</soap:Envelope>
",

options = [
Headers = [
#"Content-Type"="text/xml; charset=utf-8",
#"SOAPAction"="http://www.e-boekhouden.nl/soap/GetMutaties",
#"Authorization"="Basic " & Binary.ToText(Text.ToBinary(SessionIDpass & ":" & "xxxxxx"), BinaryEncoding.Base64)
]
],

Source = Xml.Tables(Web.Contents(Url, [
Content = Text.ToBinary(SOAPEnvelope),
Headers = options[Headers]
]))
in
Source

[jbwtp]

Hi @Kutsysteem @, This is a slightly different issue to what was dicsussed in this thread. However, this is vrery comon and there are some strategies on how to deal with thi

Try @ImkeF's post here: Quick fix for Formula.Firewall issues in Power Query and Power BI (thebiccountant.com)

Or maybe something in this thread: Formula.Firewall: Please rebuild this data combina... - Microsoft Fabric Community

Or try searching for other expamples, this is hard to tell what is going to work without having/seeing the actual code.