How to get data from a SOAP WS with Power BI

[Manal]

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

[Anonymous]

people can help me, I don't get any feedback, PBI considers it as a text

site where I'm trying to get the data from Web Service - Definição do Serviço de Exportação de Funcionários - obterFuncionarios - AHGORA SISTEM...

let
Source = Xml.Tables(Web.Contents("http://www.ahgora.com.br/ws/pontoweb.php?wsdl"
,[Content=Text.ToBinary("<soapenv:Envelope xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:ws="&Character.FromNumber(34)&"http://www.ahgora.com.br/ws"&Character.FromNumber(34)&">
<soapenv:Header/>
<soapenv:Body>
<ws:obterFuncionarios>
<hash>15151531351313135131351313531531</hash>
<matricula>230</matricula>
</ws:obterFuncionarios>
</soapenv:Body>
</soapenv:Envelope>",
Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]])))
in
Source

[Jonas1]

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? 

[juliovidigal]

Good afternoon.
Can not do everything inside the M? To test, instead of using the XML file?

EX..

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

[Jonas1]

Thanks for replying @juliovidigal . 

Yes, I can try to do that. What is the logic behind Character.FromNumber(34)? Is this something from your XML-file, or do you havew to use this in your M in order to make the urls work? 

[juliovidigal]

Inside the Text.ToBinary it plays the role of ".
Mine works with this, you can use it ... even because it's so much better than using the dento call of a file, it's all in the code.

---

[Jonas1]

I tried running the API in Postman to see if there could be anything wrong with my M, but I am getting Status code 401 there as well, so I think it is my credentials it is something wrong with. I will try to run it through the provider of the API. 

Thanks for your time though @juliovidigal , will make sure to try it out as soon as my crediantial problems has been dealt with. 

[Jonas1]

Hey @juliovidigal ,

So I finally managed to get the session ID (token) in Postman, but I think it is gonna be hard to get it to work in Power BI alone. What gets challenging is that it actually requires me to authenticate twice when doing the session ID call. Once in the XML, but also under authorization in Postman using a different username (or in request properties if you are more familiar with ReadyAPI). 

As far as I can see you then have to apply basic authorization in Power BI as well, which is not allowed when using Content in a Web.Content expression.... I guess you were able to create a anonymous connection in Power BI? 

[Yggdrasill]

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 ?

[Yggdrasill]

I've edited my previous code so I can pass parameters to the xml 

The SOAP service allows me to get maximum of 31 days of data. So I created a date parameter which I call StartingDate. Then I created a list of decimal parameter which I call Days

The date format is strictly put as YYYY-MM-DD

Then I add this code before I call the service

let
//parameters
Date2 = Date.AddDays(Date.From(#"StartingDate"), #"Days"),
    Year = Text.From(Date.Year(#"StartingDate")),
    Month= Text.From(
            if Text.Length(Number.ToText(Date.Month(#"StartingDate"))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Month(#"StartingDate"))}, "") 
                else Date.Month(#"StartingDate")
                ),
    Day =  Text.From(
            if Text.Length(Number.ToText(Date.Day(#"StartingDate"))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Day(#"StartingDate"))}, "") 
                else Date.Day(#"StartingDate")
                ),
    
    Year2 = Text.From(Date.Year(Date2)),
    Month2= Text.From(
            if Text.Length(Number.ToText(Date.Month(Date2))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Month(Date2))}, "") 
                else Date.Month(Date.AddDays(Date.From(Date2)))
                ),
    Day2 =  Text.From(
            if Text.Length(Number.ToText(Date.Day(Date2))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Day(Date2))}, "") 
                else Date.Day(Date2)
                ),
                
 //Create Date filters
 
 DateFrom = Text.Combine({Year,Month,Day}, "-") ,
 DateTo   = Text.Combine({Year2,Month2,Day2}, "-") ,

And then when it gets to the XML i simply do this

WebContent = Web.Contents(SourceURL, 
    [Content=Text.ToBinary("
    
    <soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:v1=#(0022)XXXXXXXXXX#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
      <v1:getAllLandings>
         <!--Optional:-->
         
         <from>"&DateFrom&"</from>
         <!--Optional:-->
         <to>"&DateTo&"</to>
      </v1:getAllLandings>
   </soapenv:Body>
</soapenv:Envelope>

"), 

Hope this helps 

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous]

Hi, It is possible to work around the pagenation issue with in power bi.  However, I would suggest using R/python instead unless you have to do it only in Power BI.

[Anonymous]

I created an Idea in the Ideas section to add a fully featured webservice connector.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/34017028-fully-featured-webservic...

I think this is an area where Power BI can improve. It should be easier to connect to webservices and use common features used within them.

If you agree, please vote for the idea 🙂

[erickfranz]

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"

[Anonymous]

I ve got the same issue

with the query:

   let
    body="<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:csv=#(0022)http://www.csv.org#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
        <csv:ReportTransactions>
         <csv:DateFrom>2020-01-01</csv:DateFrom>
         <csv:DateBy>2020-01-04</csv:DateBy>
         <csv:UNP>691759953</csv:UNP>
      </csv:ReportTransactions>
   </soapenv:Body>
</soapenv:Envelope>",

    Source = Xml.Tables(Web.Contents("http://XXXXX.1cws?wsdl",
    [Content=Text.ToBinary(body), 
    Headers =[
        #"Authorization" = "basic XXXX",
        #"Accept-Encoding"= "gzip,deflate",
        #"User-Agent"= "Apache-HttpClient/4.1.1 (java 1.5)",
        //#"Content-Length"="360",
        //SOAPAction="#(0022)http://www.csv.org#JI_WScsv:ReportTransactions_DD#(0022)",
        #"Content-Type"="application/soap+xml;charset=UTF-8;action=#(0022)http://www.csv.org#JI_WScsv:ReportTransactions_DD#(0022)",
        #"Connection"="Keep-Alive"]]))
in
    Source

it works in SOAP UI 

but it returns result  like no parameters are posted to service. Expanding of these tables does not give needed result

fiddler's header after SOAP UI  (when SOAP gives needed result)

Can anybody help whith it?

[Anonymous]

I have solved the problem

I needed in URL of WSDL - remove in the end "?wsdl"

let
    body="<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:csv=#(0022)http://www.csv.org#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
        <csv:ReportTransactions>
         <csv:DateFrom>2020-01-01</csv:DateFrom>
         <csv:DateBy>2020-01-04</csv:DateBy>
         <csv:UNP>691759953</csv:UNP>
      </csv:ReportTransactions>
   </soapenv:Body>
</soapenv:Envelope>",

    Source = Xml.Tables(Web.Contents("http://XXXXXXXXXXX/ws/ws_csv.1cws",
   
   // NOT http://XXXXXXXXXXX/ws/ws_csv.1cws?wsdl - 
   // this ending ?WSDL stopped the recieving of the response!


   [Content=Text.ToBinary(body), 
	Headers =[
        #"Authorization" = "basic XXXXXXXXXX",
        #"Accept-Encoding"= "gzip,deflate",
        #"Content-Type"="text/xml;charset=UTF-8"
           ]])),
    Table = Source{1}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table]
in

[Anonymous]

@Jonas1 

@juliovidigal 

@Yggdrasill 

@amitchandak 

Hi All 

I am facing issues while accessing the token bassed SOAP WSDL in Power BI.

Also any other way to accomplish this task part from Zapsys ODBC

Error 

DataSource.Error: Web.Contents failed to get contents from 'https://sync.severa.com/webservice/S3/API.svc' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://sync.severa.com/webservice/S3/API.svc
Url=https://sync.severa.com/webservice/S3/API.svc

Code in Advance Editor 

let

Source = Xml.Tables(Web.Contents("https://sync.XXXXXX/S3/API.svc"
,[Content=File.Contents("C:\XX\post.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

post.XML

<?xml version="1.0"?>

-<soapenv:Envelope xmlns:soap="http://soap.severa.com/" xmlns:ns1="http://soap.severa.com/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">-<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>

[Anonymous]

Have you tried to get data with SOAP UI or POSTMAN software?

If it is OK - then I recommend to watch carefully for the headers in POST metod.

[Anonymous]

Yes its working in SOAP UI and I have created above mentioned xml and query by following this thread.

[Anonymous]

Then it should work.

I had a trouble in some unnoticable things - like conncetion string ending...

Also I do not see that in your XML you use #(0022)  instead of " . See my example below - may the trouble in it.

body="<soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022)