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 :
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): 
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) :
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