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
Helper I
Helper I

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: 

image.png

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

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

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? 

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.


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. 

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? 

Resolver III
Resolver III

 

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 ?

 

 

 

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
Not applicable

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
Not applicable

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. 

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
Not applicable

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 🙂

 

 

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"

 

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

webservice_not_posted_parameters.jpg

 

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

fiddler_post_header.jpg

 

Can anybody help whith it?

 

 
Frequent Visitor

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
Not applicable

@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>

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
Not applicable

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

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)

Anonymous
Not applicable

I am not using #022 in soap UI and also I made this by review previous posts and they are not using this.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

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