How to get data from a SOAP WS with Power BI

Hi,

I think we found somehting :

we created an XML file (poste.xml) with the info for the SOAP web service : 

then in the advanced editor of the query editor we add the path to the file :

let
Source = Xml.Tables(Web.Contents("URLofthewebservice",[Content=File.Contents("C:\Users\username\Desktop\XX\post.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]])),

previously we used "get data" --"web"---- URLofthewebservice. you drill down untill you get a table, you can either load or modify the query. 

Well, the world has kind of moved on from SOAP and to my knowledge there is not any SOAP accessor function in Power Query:

https://msdn.microsoft.com/en-us/library/mt296615.aspx

A couple thoughts I had, use an ODBC driver for SOAP such as here:

http://www.drdobbs.com/windows/odbc-driver-development/184416434

The other thought that I had was a SOAP to OData proxy but I really can't find one out there, all I could find was how to convert from SOAP to OData:

http://www.codeproject.com/Articles/590627/Converting-SOAP-based-WCF-Service-to-RESTful-Desig

On a personal note, I am not sorry to see SOAP go away, it was seemed like a terribly costly data protocol in terms of overhead and I always stuck with RESTful services. I have a general idea that my feelings were shared by the majority of people out there and hence why SOAP has largely gone away.

I use this query: 

let
Source = Xml.Tables(Web.Contents("xxxxxxxxxxxx"
,[Content=File.Contents("xxxxxxx\post.xml"),Headers=[Authorization="Basic xxxxxxxxxxx" ,#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

The Authorization is OK. My post.xml is:

<soap:Envelope xmlns:urn="urn:sap-com:document:xxxxxxxx" xmlns:soap-envelope="http://www.w3.org/2003/05/soap-envelope" >
<soap:Header/>
<soap:Body>
<urn:ZimStockProl>
<IAlmacen>xx</IAlmacen>
<ICentro>xx</ICentro>
</urn:ZimStockProl>
</soap:Body>
</soap:Envelope> 

I try it in SOAPUI and is OK, but the result in Power BI is different:

I think It must be the dirección "urn"

I use this query: 

let
Source = Xml.Tables(Web.Contents("xxxxxxxxxxxx"
,[Content=File.Contents("xxxxxxx\post.xml"),Headers=[Authorization="Basic xxxxxxxxxxx" ,#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

The Authorization is OK. My post.xml is:

<soap:Envelope xmlns:urn="urn:sap-com:document:xxxxxxxx" xmlns:soap-envelope="http://www.w3.org/2003/05/soap-envelope" >
<soap:Header/>
<soap:Body>
<urn:ZimStockProl>
<IAlmacen>xx</IAlmacen>
<ICentro>xx</ICentro>
</urn:ZimStockProl>
</soap:Body>
</soap:Envelope> 

I try it in SOAPUI and is OK, but the result in Power BI is different:

I think It must be the dirección "urn"

I use this query: 

let
Source = Xml.Tables(Web.Contents("xxxxxxxxxxxx"
,[Content=File.Contents("xxxxxxx\post.xml"),Headers=[Authorization="Basic xxxxxxxxxxx" ,#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

The Authorization is OK. My post.xml is:

<soap:Envelope xmlns:urn="urn:sap-com:document:xxxxxxxx" xmlns:soap-envelope="http://www.w3.org/2003/05/soap-envelope" >
<soap:Header/>
<soap:Body>
<urn:ZimStockProl>
<IAlmacen>xx</IAlmacen>
<ICentro>xx</ICentro>
</urn:ZimStockProl>
</soap:Body>
</soap:Envelope> 

I try it in SOAPUI and is OK, but the result in Power BI is different:

I think It must be the dirección "urn"

I use this query: 

let
Source = Xml.Tables(Web.Contents("xxxxxxxxxxxx"
,[Content=File.Contents("xxxxxxx\post.xml"),Headers=[Authorization="Basic xxxxxxxxxxx" ,#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

The Authorization is OK. My post.xml is:

<soap:Envelope xmlns:urn="urn:sap-com:document:xxxxxxxx" xmlns:soap-envelope="http://www.w3.org/2003/05/soap-envelope" >
<soap:Header/>
<soap:Body>
<urn:ZimStockProl>
<IAlmacen>xx</IAlmacen>
<ICentro>xx</ICentro>
</urn:ZimStockProl>
</soap:Body>
</soap:Envelope> 

I try it in SOAPUI and is OK, but the result in Power BI is different:

I think It must be the dirección "urn"

I gave up on extracting SOAP trough Power BI directly. I now use Python scripts and load the output of those into Power BI. The problem I ran into was pagination.

The main transaction table in my soap source is 1 million rows. But I can only request 5000 records of either a month or a week of a certain year at the same time. In Python it is very easy to make the request variable just by using SOAP wrappers available in the python repository. I couldn't figure out how to do it in Power BI. 

In the Netherlands we have a some really popular webservices used by many small to medium companies but unfortunately some of the largest use the legacy SOAP. 

After getting into it more and more I have to say its getting easier. I have been able to build functioning connectors on several SOAP webservices now thanks to the people in this thread, other threads and some hard trial and error.

Today I had a service that in a response gives only 100 records. You need to input:

- Mark (timestamp value from which timestamp onwards you want to receive)

The response gives:

- Data (100 records)

- Mark (Timestamp value)

- More (Whether more data is available)

The service will send the first 100 records that have never been seen before by me first if you use the correct mark. The correct mark is the mark used in the last response of the server. If you use the incorrect mark it will start sending all data never seen by me up untill the mark and all data after the mark regardless whether I have seen it or not.

I have been struggling all day to figure out a way to use the mark sent by the server in my next request and eval the more value in the response,

In below example, GetTraces(mark as text, customer as text, creds as text) is a function that collects a response from the server with a mark of a specific customer number, creds contains the basis authentication base encoded value,

This is what I came up with in the end to keep generating requests with the correct mark untill the more value is false:, may it help someone

a = List.Generate( () => [Page = GetTraces(mark, customer, creds)],
            each [Page]{0}[more] <> "false",
            each [Page = GetTraces([Page]{0}[mark], customer, creds)]

I process the resulting list further into the correct table in the Power BI GUI and past the resulting code in my connector code.

This thread is very useful.

My datasource requires my IP and gives me user and password in return.

This is my code and it returns data and works.

let
SourceURL = "url of wsdl",

options = [ #"Authorization" ="Basic XXXX",
            #"Accept-Encoding"= "gzip,deflate",
           // SOAPAction="",
            #"Content-Type"="text/xml;charset=UTF-8",
            #"Connection"="Keep-Alive"
          ],
WebContent = Web.Contents(SourceURL, 
    [Content=Text.ToBinary("
    
    <soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) 
   xmlns:v1=#(0022)Host URL#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
      <v1:getAll>
         <!--Optional:-->
         <from>2018-10-10T00:00:00Z</from>
         <!--Optional:-->
         <to>2018-10-10T00:00:00Z</to>
      </v1:getAll>
   </soapenv:Body>
</soapenv:Envelope>

"), 
Headers=options]) ,
XmlContent = Xml.Tables(WebContent),
    Table = XmlContent{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    Table4 = Table3{0}[Table],
in
    Table4

I'm trying to pass a parameter to the date filters in the xml text. 

Anyone having any luck with that ?

Been trying what you recommend in this thread for quite osme time now, but I am getting "Can not approve with the given credentials. Try again" when I am trying to retrieve my session token. My credentials are given in the xml-file while the connection type has been set to anonymous, so this should not make sense right? 

I use the following code in the query: 

let
    Source = Xml.Tables(Web.Contents("https://xxx.xxxxx.com/wsapi/services/authentication?WSDL",[Content=File.Contents("xxxx\xxxxx\xxxxx.xml"),
    Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
    Source

While the xml-file is the login-request for the api, only returning my username and password when I import the file to Power Bi independently: 

Can you see anything wrong I am doing? Could JasonL  or juliovidigal  have a quick look for instance?