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.
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 )
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:
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.
Can you please share your next SOAP request with dummy key. Just to check how should I construct. I was trying as bellow.
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)&">
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
I was using Anonymous as credentials to get the session id which was working. Can u please guide me?
this will probably solve my request problem:
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"]]))
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 ..
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 ))
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">
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")
Well, the world has kind of moved on from SOAP and to my knowledge there is not any SOAP accessor function in Power Query:
A couple thoughts I had, use an ODBC driver for SOAP such as here:
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:
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.
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.
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.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.