Forum Discussion

Manal's avatar
Manal
Regular Visitor
10 years ago

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 i...
Need Help
Yggdrasill's avatar
Yggdrasill
Icon for Responsive Resident rankResponsive Resident
7 years ago

 

This thread is very useful.

My datasource requires my IP and gives me user and password in return.

 

This is my code and it returns data and works.

 

 

let
SourceURL = "url of wsdl",

options = [ #"Authorization" ="Basic XXXX",
            #"Accept-Encoding"= "gzip,deflate",
           // SOAPAction="",
            #"Content-Type"="text/xml;charset=UTF-8",
            #"Connection"="Keep-Alive"
          ],
WebContent = Web.Contents(SourceURL, 
    [Content=Text.ToBinary("
    
    <soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) 
   xmlns:v1=#(0022)Host URL#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
      <v1:getAll>
         <!--Optional:-->
         <from>2018-10-10T00:00:00Z</from>
         <!--Optional:-->
         <to>2018-10-10T00:00:00Z</to>
      </v1:getAll>
   </soapenv:Body>
</soapenv:Envelope>

"), 
Headers=options]) ,
XmlContent = Xml.Tables(WebContent),
    Table = XmlContent{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    Table4 = Table3{0}[Table],
in
    Table4

 

I'm trying to pass a parameter to the date filters in the xml text. 

 

Anyone having any luck with that ?

 

 

 

  • Yggdrasill's avatar
    Yggdrasill
    Icon for Responsive Resident rankResponsive Resident
    7 years ago

    I've edited my previous code so I can pass parameters to the xml 

     

    The SOAP service allows me to get maximum of 31 days of data. So I created a date parameter which I call StartingDate. Then I created a list of decimal parameter which I call Days

     

    The date format is strictly put as YYYY-MM-DD


    Then I add this code before I call the service

     

    let
    //parameters
Date2 = Date.AddDays(Date.From(#"StartingDate"), #"Days"),
    Year = Text.From(Date.Year(#"StartingDate")),
    Month= Text.From(
            if Text.Length(Number.ToText(Date.Month(#"StartingDate"))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Month(#"StartingDate"))}, "") 
                else Date.Month(#"StartingDate")
                ),
    Day =  Text.From(
            if Text.Length(Number.ToText(Date.Day(#"StartingDate"))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Day(#"StartingDate"))}, "") 
                else Date.Day(#"StartingDate")
                ),
    
    Year2 = Text.From(Date.Year(Date2)),
    Month2= Text.From(
            if Text.Length(Number.ToText(Date.Month(Date2))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Month(Date2))}, "") 
                else Date.Month(Date.AddDays(Date.From(Date2)))
                ),
    Day2 =  Text.From(
            if Text.Length(Number.ToText(Date.Day(Date2))) = 1 
                then Text.Combine({"0",Number.ToText(Date.Day(Date2))}, "") 
                else Date.Day(Date2)
                ),
                
 //Create Date filters
 
 DateFrom = Text.Combine({Year,Month,Day}, "-") ,
 DateTo   = Text.Combine({Year2,Month2,Day2}, "-") ,

    And then when it gets to the XML i simply do this

     

    WebContent = Web.Contents(SourceURL, 
    [Content=Text.ToBinary("
    
    <soapenv:Envelope xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:v1=#(0022)XXXXXXXXXX#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
      <v1:getAllLandings>
         <!--Optional:-->
         
         <from>"&DateFrom&"</from>
         <!--Optional:-->
         <to>"&DateTo&"</to>
      </v1:getAllLandings>
   </soapenv:Body>
</soapenv:Envelope>

"),

    Hope this helps