cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

How to get data from a SOAP WS with Power BI

Hello,

 

I have an application with a SOAP wb and I want to create Dashboards using Power BI Desktop.

How do I get data using the SOAP wb ? 

I know that Power BI has a REST API and it says that it helps you push data into power bi resource but in my case I have an external resource I have to connect to.

 

Thank you for your help.

 

Best regards.

Manal

58 REPLIES 58

As @Bulat mentions, you need to replace the quote marks " from the SOAP UI envelope to #(0022) like so:

<?xml version="1.0"?>

-<soapenv:Envelope xmlns:soap=#(0022)http://soap.severa.com/#(0022) xmlns:ns1=#(0022)http://soap.severa.com/#(0022) xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022)>-<soapenv:Header>

<ns1:WebServicePassword>Api Token Key</ns1:WebServicePassword>

</soapenv:Header>-

  <soapenv:Body>-

    <soap:GetHourEntriesByDate>

 

      <soap:startdate>2020-04-12T09:00:00</soap:startdate>

<!--Optional:--></soap:GetHourEntriesByDate>

</soapenv:Body>

</soapenv:Envelope>

 

 

If you can connect via SOAP UI and not Power BI, you're doing something wrong in the PQ (advanced editor)

Like a few of the others, I am also having an issue where I just end up with this

 

sanfly_0-1598696790849.png

 

and I'm not sure how to troubleshoot.  Can anyone assist?

 

let
    SourceURL = "http://soap.proofhq.com/19_2_6/soap.wsdl",

options = [ #"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("
    <?xml version='1.0' encoding='UTF-8'?>
   <soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:soap=#(0022)https://soap.proofhq.com/#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
      <soap:doLogin>
         <Login>me@mydomain.com</Login>
         <Password>jy@%MZdI#O7gikwl23dKI@IG9!5W</Password>
         <Subdomain>mysubdomain</Subdomain>
      </soap:doLogin>
   </soapenv:Body>
</soapenv:Envelope>
"), 
Headers=options]) ,
XmlContent = Xml.Tables(WebContent)
in
    XmlContent

Good Morning.
What should your output be?

In the ones I did in the past, I always used XML.Tables ....

let

Source = Xml.Tables(Web.Contents("https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL"

,[Content=Text.ToBinary("<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https://advertising.criteo.com/API/v201305"&Character.FromNumber(34)&" xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&">

<soapenv:Body>

<clientLogin>

<username>aaaaa@aaaa.com</username>

<password>aaaaaaaa</password>

</clientLogin>

</soapenv:Body>

</soapenv:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))

in

Source

Hi,

 

I am new to Power BI and also looking for how to get data from SOAP based WCF services in PowerBI. I am able to get the data using ZappySys xml driver. Can you please provide me screenshots how to achieve without using xml driver?

Hi Eric, I think you need to expand the "table" in the results in order to see the results you are expecting.

New Member

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:

 

Captura.JPG

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

 

New Member

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:

Result

 

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

 

New Member

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:

Result

 

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

 

Advocate II
Advocate II

Hi,

 

I think we found somehting :

 

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

 Sans titre.jpg

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. 

Hi all,

 

I am getting the following error.

 

DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: The 'link' start tag on line 31 position 6 does not match the end tag of 'head'. Line 80, position 3.)
Details:
    Binary

 

Here is my Advanced Editor code:

 

let
    Source = Xml.Tables(Web.Contents("https://sandbox......./", [Content=File.Contents("C:\Users\....\get_default.xml"),Headers=[Authorization="Bearer 34653fdsgfdsffd", #"Content-Type"="text/xml", SOAPAction="http://....../get.........Details"]]))
in
    Source

 

Here is my Xml code:

 

<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' xmlns:ins='http://beans....._v3'>
  <soapenv:Header/>
  <soapenv:Body>
  <ins:removedforprivacy>
  <startDate>2012-10-17</startDate>
  <endDate>2012-10-22</endDate>
  <pagingInfo>
  <pageNumber>0</pageNumber>
  <pageSize>5</pageSize>
  </pagingInfo>
  </ins:removedforprivacy>
</soapenv:Body>
</soapenv:Envelope>

This code works in R using RCurl, I have also tried it with the addition of <?xml version="1.0"?> at the start. I also tried it using the Content=Text.ToBinary(".....").

 

Could the issue be with the file being returned?

 

Thank you

🙂

 

 Edit 1: grammar etc.

 

Edit 2: The issue was the the main url - the inititial url used to set up the web access and a secondary used in the advanced editor.  This was followed up by another issue where the data would not be loaded out of the query editor... This was solved by making sure the page size fit the data limit.

Apparently it's a problem with the return, even by the mention of lines 31 and 80, and your call does not have that size.

thank you for responding, I have figured out the problem.. I will edit my post to relect it.

Were you able to make this approach work? We are also trying to call and load the a SOAP response from PowerBI without sucess.  I read through the other messages stating that SOAP is a dying technology - however we do not have control over the software providing the data.

Hello dataloreous,

 

Yes we got it to work. Yes soapy SOAP is old and we are also stuck in the past... the joys of working for the governement...

 

This being said :

  1. Our SOAP web service return an XML file and need a user and a PW and some other info to get the data we want.
  2. You need to start somewhere and get data : either with an XML file or with the web service's URL. (its doesnt has any importance because we just want to get to the query editor (Power Query)
  3. So we started to get data from an XML file because we already tested our web service with an other app and we got the XML it returns, so we loaded it. And drilled down till we get something to load/modify :2.PNG
  4. As I said, you can simply load the web service URL and use "WEB" to get data. The goal is to load something then access the query editor and advanced editor.
  5. In the query editor, you accesse the advanced editor (sorry for my french): 3.PNG
  6. in the advanced editor you change your source to :

    let
    Source = Xml.Tables(Web.Contents("URLofTheWebService",[Content=File.Contents("D:\download\post.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]])),

  7. We use Xml.Tables because as I said our web service return an Xml and web.contents because we connect to a web service
  8. "D:\download\post.xml" is the path to the XML file where we have the credentials to connect to the web service, you have to call it in your query, here's an example of the file (made with SOAP UI open source) :4.PNG
  9.   The rest of the query code depends on the structur of your XML, here's what we have :
  10. Table = Source{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    Table4 = Table3{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table4,{{"chaineXml", type text}, {"codeRetour", Int64.Type}, {"codeSysteme", type text}, {"messageRetour", type text}, {"nbrErreurs", Int64.Type}, {"noFormulaire", Int64.Type}, {"nomSysteme", type text}, {"version", Int64.Type}}),
    chaineXml1 = #"Changed Type"{0}[chaineXml],
    #"Parsed XML" = Xml.Tables(chaineXml1),
    Table5 = #"Parsed XML"{0}[Table],
    Table6 = Table5{0}[Table],
    #"Expanded Namespace:" = Table.ExpandTableColumn(Table6, "Namespace:", {"column1", "column2", "columnXX"}, {"Namespace:.column1", "Namespace:.column2", "Namespace:.columnXX"}),
    #"Expanded Namespace:.column1" = Table.ExpandTableColumn(#"Expanded Namespace:", "Namespace:.column1", {"column2", "column3", "columnXX""}, {"Namespace:.column1.column2", "Namespace:.column1.column3", "Namespace:.column1.columnXX"})
    in
    #"Expanded Namespace:.column1"
  11. Our code is structured like that because the XML file we get has all the data as XML text in one feild so we have to parse de data and transform the XML string in a table

 

Hope this help!

 

Jason

I Try.

https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL

 

XML saved  in c:\a


<soapenv:Envelope xmlns:v20="https://advertising.criteo.com/API/v201305" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
<v20:clientLogin>
<!--Optional:-->
<v20:username>XXXXXX</v20:username>
<!--Optional:-->
<v20:password>XXXXX</v20:password>
<!--Optional:-->
</v20:clientLogin>
</soapenv:Body>
</soapenv:Envelope>

Advanced Editor

let

Source = Xml.Tables(Web.Contents("https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL"
,[Content=File.Contents("C:\a\a.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source


Return this error..

 

DataSource.Error: Web.Contents failed to get contents from 'https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://advertising.criteo.com/API/v201305/AdvertiserService.asmx
Url=https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL

 

Can you help me?

 

Tanks



Your M code seems ok, the only difference I see with me is in your XML

you use <v20:clientLogin>

 

were i use </web:returnData>

I've removed the "SOAPAction =" "and now it's working.
Thank you

It seems that making SOAP more user firendly for PowerBI is because of lack of Xml create functions.

Has anyone tried to build an populate an Xml.Document type as per the MSDN document? https://msdn.microsoft.com/en-us/library/mt260907.aspx

Maybe if you start from there you can resolve a lot of SOAP comunication issues.

Actually, the next step of my project was this, because I'm going to publish this and I do not find it interesting to keep calling files to make the call.
So now I've put the XML inside PowerQuery.

Because I'm going to use the token return on other calls, it's unfeasible that it stays on file.

It looks like this:

let

    Source = Xml.Tables(Web.Contents("https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL"

    ,[Content=Text.ToBinary("<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https://advertising.criteo.com/API/v201305"&Character.FromNumber(34)&" xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&">

   <soapenv:Body>

      <clientLogin>

         <username>aaaaa@aaaa.com</username>

         <password>aaaaaaaa</password>

      </clientLogin>

   </soapenv:Body>

</soapenv:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))

in

    Source

Might want to consider using single quotes in your xml. Or if you need those double quoutes try using #(0022) in your query it is equivalent to Character.FromNumber(34), plus you don't need to concatenate them.

instead of

"<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https"

try

"<soapenv:Envelope xmlns=#(0022)https"

 

Plus, have you considered using Text.Format to keep password and username out of the XML qurey builder?

Good evening. I used your tips, and they really worked, the formatting was simpler. Thanks for the days. Regarding using the text.format, I really did not understand how to apply this. The idea is to receive this as a parameter? So you do not get stuck in the code? Thank you

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors