Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Manal
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

61 REPLIES 61

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

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" 

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.