Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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 🙂
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.