cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

57 REPLIES 57
Highlighted
Super User IX
Super User IX

Re: How to get data from a SOAP WS with Power BI

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.


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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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!




Highlighted
Regular Visitor

Re: How to get data from a SOAP WS with Power BI

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.

Highlighted
Regular Visitor

Re: How to get data from a SOAP WS with Power BI

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.

Highlighted
Advocate II
Advocate II

Re: How to get data from a SOAP WS with Power BI

Hi,

 

I think we found somehting :

 

we created an XML file (poste.xml) with the info for the SOAP web service : 

 Sans titre.jpg

then in the advanced editor of the query editor we add the path to the file :

 

let
Source = Xml.Tables(Web.Contents("URLofthewebservice",[Content=File.Contents("C:\Users\username\Desktop\XX\post.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]])),

 

previously we used "get data" --"web"---- URLofthewebservice. you drill down untill you get a table, you can either load or modify the query. 

Highlighted
Frequent Visitor

Re: How to get data from a SOAP WS with Power BI

Were you able to make this approach work? We are also trying to call and load the a SOAP response from PowerBI without sucess.  I read through the other messages stating that SOAP is a dying technology - however we do not have control over the software providing the data.

Highlighted
Advocate II
Advocate II

Re: How to get data from a SOAP WS with Power BI

Hello dataloreous,

 

Yes we got it to work. Yes soapy SOAP is old and we are also stuck in the past... the joys of working for the governement...

 

This being said :

  1. Our SOAP web service return an XML file and need a user and a PW and some other info to get the data we want.
  2. You need to start somewhere and get data : either with an XML file or with the web service's URL. (its doesnt has any importance because we just want to get to the query editor (Power Query)
  3. So we started to get data from an XML file because we already tested our web service with an other app and we got the XML it returns, so we loaded it. And drilled down till we get something to load/modify :2.PNG
  4. As I said, you can simply load the web service URL and use "WEB" to get data. The goal is to load something then access the query editor and advanced editor.
  5. In the query editor, you accesse the advanced editor (sorry for my french): 3.PNG
  6. in the advanced editor you change your source to :

    let
    Source = Xml.Tables(Web.Contents("URLofTheWebService",[Content=File.Contents("D:\download\post.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]])),

  7. We use Xml.Tables because as I said our web service return an Xml and web.contents because we connect to a web service
  8. "D:\download\post.xml" is the path to the XML file where we have the credentials to connect to the web service, you have to call it in your query, here's an example of the file (made with SOAP UI open source) :4.PNG
  9.   The rest of the query code depends on the structur of your XML, here's what we have :
  10. Table = Source{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    Table4 = Table3{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table4,{{"chaineXml", type text}, {"codeRetour", Int64.Type}, {"codeSysteme", type text}, {"messageRetour", type text}, {"nbrErreurs", Int64.Type}, {"noFormulaire", Int64.Type}, {"nomSysteme", type text}, {"version", Int64.Type}}),
    chaineXml1 = #"Changed Type"{0}[chaineXml],
    #"Parsed XML" = Xml.Tables(chaineXml1),
    Table5 = #"Parsed XML"{0}[Table],
    Table6 = Table5{0}[Table],
    #"Expanded Namespace:" = Table.ExpandTableColumn(Table6, "Namespace:", {"column1", "column2", "columnXX"}, {"Namespace:.column1", "Namespace:.column2", "Namespace:.columnXX"}),
    #"Expanded Namespace:.column1" = Table.ExpandTableColumn(#"Expanded Namespace:", "Namespace:.column1", {"column2", "column3", "columnXX""}, {"Namespace:.column1.column2", "Namespace:.column1.column3", "Namespace:.column1.columnXX"})
    in
    #"Expanded Namespace:.column1"
  11. Our code is structured like that because the XML file we get has all the data as XML text in one feild so we have to parse de data and transform the XML string in a table

 

Hope this help!

 

Jason

Highlighted
Advocate II
Advocate II

Re: How to get data from a SOAP WS with Power BI

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" 

Highlighted
Anonymous
Not applicable

Re: How to get data from a SOAP WS with Power BI

Hi @Manal,

 

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

 

Greetings,

Ronald

 

Highlighted
Frequent Visitor

Re: How to get data from a SOAP WS with Power BI

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.

 

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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.

Top Kudoed Authors