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

Hi check below blog post. It explains concepts of calling any Web service in Power BI. Basically, you need to craft SOAP Body for POST request and need to supply credentials / http headers (e.g. Content-Type, SOAPAction ) 

 

https://zappysys.com/blog/call-soap-api-power-bi-read-xml-web-service-data/

 

Configure ZappySys XML ODBC DSNConfigure ZappySys XML ODBC DSNPreview SOAP API query using ZappySys XML DriverPreview SOAP API query using ZappySys XML DriverImport SOAP API data in Power BI using ODBC connectionImport SOAP API data in Power BI using ODBC connection

Sample SQL to import SOAP Web Service Data in Power BI

 

SELECT * FROM $
WITH(
	 src='http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx'
	,DataConnectionType='HTTP'
	,CredentialType='Basic' --OR SoapWss
	,SoapWssPasswordType='PasswordText'
	,UserName='myuser'
	,Password='pass$$w123'
	,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'
	,ElementsToTreatAsArray='HolidayCode'	
	,RequestMethod='POST'	
	,Header='Content-Type: text/xml;charset=UTF-8 || SOAPAction: "http://www.holidaywebservice.com/HolidayService_v2/GetHolidaysAvailable"'
	,RequestData='
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:hol="http://www.holidaywebservice.com/HolidayService_v2/">
   <soapenv:Header/>
   <soapenv:Body>
      <hol:GetHolidaysAvailable>
         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->
         <hol:countryCode>UnitedStates</hol:countryCode>
      </hol:GetHolidaysAvailable>
   </soapenv:Body>
</soapenv:Envelope>'
)

Text.Format would help you to store username password in some parameter and pass it to the XML builder routine. Something like this:


Text.Format("longxml

<username>#[username]</username>
<password>#[password]</password>
more xml",[username = parameter_username, password= parameter_password])

Thank you for the tip.
I will certainly use it in other scenarios. In this scenario, I only use username and password at that moment, then I use the Token that I get from this authentication, and that email and password will not change.

Thank you

Can you please share your next SOAP request with dummy key. Just to check how should I construct. I was trying as bellow.

let
Source = Xml.Tables(Web.Contents("https://api1.xxx.com/services/MainService?wsdl"
,[Content=Text.ToBinary("<x:Envelope xmlns:x="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:ser="&Character.FromNumber(34)&"http://services"&Character.FromNumber(34)&">
<x:Header/>

<x:Body>
<ser:getHistory>
<ser:sessionId>xxxxxxxxxx</ser:sessionId>
<ser:entityTypeId>4</ser:entityTypeId>
<ser:startYYYYMMDD>2016/08/04 05:49:05</ser:startYYYYMMDD>
<ser:endYYYYMMDD>2016/08/17 07:33:46</ser:endYYYYMMDD>
<ser:showRepeatingPerDay>false</ser:showRepeatingPerDay>
<ser:simpleDateFormat>yyyy/MM/dd HH:mm:ss</ser:simpleDateFormat>
</ser:getHistory>
</x:Body>
</x:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

But this start giving following error.
DataSource.Error: Web.Contents failed to get contents from 'https://api1.innotas.com/services/MainService?wsdl' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://api1.innotas.com/services/MainService
Url=https://api1.innotas.com/services/MainService?wsdl

I was using Anonymous as credentials to get the session id which was working. Can u please guide me?

Good morning, sorry for the delay.

Is this the first call, or do you want to use her return to populate the sessionId?

Ok,

 

this will probably solve my request problem:

 

let

Source = Xml.Tables(Web.Contents("https://myWSDL"
,[Content=File.Contents("PathToMyXmlfile.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source

 

But the response is a json file. There's a way to parse it?

In the visual layer of PowerBI, you have to give a parse ..

But if it is your case, I have a scenario that I call an API that returns a json file, and I treat it as follows ..

 

 

let
SourceTESTE = "http://mediamanager.voxus.tv/api/?method=detailed&token=XXXXXX&start_date=" & "20/11/2016" & "&end_date=" & "30/12/2050",
Fonte = Json.Document(Web.Contents(SourceTESTE )) 

......

I'm going to do a test using </ web: returnData> ...

Tanks

Have you tried to connecte to your web service with something else than Power Bi to see if it works?

I can test it from my office, proxy block advertisement website

Also are you sure your webservice returns XML?

Yes, it is working, I have tested using SoapUI, and I have had the correct return in XML.
This is the return in SoapUI...!

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<clientLoginResponse xmlns="https://advertising.criteo.com/API/v201305">
<clientLoginResult>XXXXXXXXXXXXX</clientLoginResult>
</clientLoginResponse>
</soap:Body>
</soap:Envelope>

Thank you for the very thorough reply. We had created a PowerShell script to call the XML and save on the local drive, which was then parsed by PowerQuery.  This was too many clicks for the user however so this approach is preferred.

 

The advanced query editor opens the door to pretty much anything when you understand how it works and how Power Bi works (I mean by that all the mecanismes that are under the "hood")

An other way to parse XML without using code : 

the data is all ine one feild as an XML stringthe data is all ine one feild as an XML stringright-click on the feildright-click on the feildclick on "Analyse"click on "Analyse"click on "Table"click on "Table"click on this, always forget the name lolclick on this, always forget the name lolif you have all your column's name click ok and let the magic happenif you have all your column's name click ok and let the magic happen

 

If you still ahve data in one feild, just click on "Table" 

Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Any recommendation to parse XML query from a server? in my use case I should send date time range to the server and request back the data from the server.

Thank you!

After some more researches I found this : https://www.dreamfactory.com/content/turn-soap-rest

It seems like DreamFactory can turn a SOAP into REST WS. I will test it and see if it does work for me.

Anonymous
Not applicable

Hi @Manal,

 

I'am wondering if the DreamFactory approach worked? Cloud you share your experiences?

 

Greetings,

Ronald

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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