How to connect to a SOAP API in Power BI or Power Query

In this video I will show you how to connect to a SOAP API in Power BI using Power Query.

Watch the tutorial on YouTube

The code:

let

url = "https://www.w3schools.com/xml/tempconvert.asmx",

SOAPEnvelope = 
"<soap:Envelope xmlns:xsi=#(0022)http://www.w3.org/2001/XMLSchema-instance#(0022) xmlns:xsd=#(0022)http://www.w3.org/2001/XMLSchema#(0022) xmlns:soap=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022)>
  <soap:Body>
    <CelsiusToFahrenheit xmlns=#(0022)https://www.w3schools.com/xml/#(0022)>
      <Celsius>"&Celcius&"</Celsius>
    </CelsiusToFahrenheit>
  </soap:Body>
</soap:Envelope>",

options = [
    #"Content-Type"="text/xml;charset=utf-8"
],

    Source = Xml.Tables(Web.Contents(url, [Content=Text.ToBinary(SOAPEnvelope), Headers = options])),
    Table = Source{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table]
in
    Table2

You can test this code on this service:

https://www.w3schools.com/xml/tempconvert.asmx?op=CelsiusToFahrenheit

Basic authentication:

If you need to authenticate yourself when connecting to the SOAP API, you need to add the authentication to the headers on the function.

Imagine your credentials are:

Username: user
Password: pass

Your need to encode (base64) your credentials, so use:

Binary.ToText(Text.ToBinary(“user:pass”),0)

which will result into dXNlcjpwYXNz and add it now to the Authentication variable:

let

url = "https://www.w3schools.com/xml/tempconvert.asmx",

SOAPEnvelope = 
"<soap:Envelope xmlns:xsi=#(0022)http://www.w3.org/2001/XMLSchema-instance#(0022) xmlns:xsd=#(0022)http://www.w3.org/2001/XMLSchema#(0022) xmlns:soap=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022)>
  <soap:Body>
    <CelsiusToFahrenheit xmlns=#(0022)https://www.w3schools.com/xml/#(0022)>
      <Celsius>"&Celcius&"</Celsius>
    </CelsiusToFahrenheit>
  </soap:Body>
</soap:Envelope>",

options = [
    #"Authentication" = "Basic dXNlcjpwYXNz",
    #"Content-Type"="text/xml;charset=utf-8"
],

    Source = Xml.Tables(Web.Contents(url, [Content=Text.ToBinary(SOAPEnvelope), Headers = options])),
    Table = Source{0}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{0}[Table]
in
    Table2