How to connect Power BI to a data source in a Web Service

[sebastianslz]

I am starting with Power BI and now I am faced with the challenge of connecting to a data source from a WebService made on asmx, the WebService address is:

http://www.testws.com:65/com_InfoListAll.asmx

When entering the previous address from the browser, the information that my Web Service contains is the following:

Com_InfoListAll

The following operations are supported. For a formal definition, please review the Service Description.

 - InfoTravel
 - ShowList

This web service is using http://tempuri.org/ as its default namespace.
Recommendation: Change the default namespace before the XML Web service is made public.
Each XML Web service needs a unique namespace in order for client applications to distinguish it from other services on the Web. http://tempuri.org/ is available for XML Web services that are under development, but published XML Web services should use a more permanent namespace.

Your XML Web service should be identified by a namespace that you control. For example, you can use your company's Internet domain name as part of the namespace. Although many XML Web service namespaces look like URLs, they need not point to actual resources on the Web. (XML Web service namespaces are URIs.)

For XML Web services creating using ASP.NET, the default namespace can be changed using the WebService attribute's Namespace property. The WebService attribute is an attribute applied to the class that contains the XML Web service methods. Below is a code example that sets the namespace to "http://microsoft.com/webservices/":

C#

[WebService(Namespace="http://microsoft.com/webservices/")]
public class MyWebService {
    // implementation
}
Visual Basic

<WebService(Namespace:="http://microsoft.com/webservices/")> Public Class MyWebService
    ' implementation
End Class
C++

[WebService(Namespace="http://microsoft.com/webservices/")]
public ref class MyWebService {
    // implementation
};
For more details on XML namespaces, see the W3C recommendation on Namespaces in XML.

For more details on WSDL, see the WSDL Specification.

For more details on URIs, see RFC 2396.

It is clearly evident that I use two methods, which are InfoTravel and ShowList

The direction of the method InfoTravel is:

http://www.testws.com:65/com_InfoListAll.asmx?op=InfoTravel

The direction of the method ShowListis:

http://www.testws.com:65/com_InfoListAll.asmx?op=ShowList

The content of the InfoTravel and ShowList method is the same, just change the parameters,  that's why I add only what is shown in the ShowList method.

Com_InfoListAll


Click here for a complete list of operations.

ShowList

Test
To test the operation using the HTTP POST protocol, click the 'Invoke' button.

Parameter   Value
username:   
password:
    
SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.

POST /com_InfoListAll.asmx HTTP/1.1
Host: www.testws.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/ShowList"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <ShowList xmlns="http://tempuri.org/">
      <username>string</username>
      <password>string</password>
    </ShowList>
  </soap:Body>
</soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <ShowListResponse xmlns="http://tempuri.org/">
      <ShowListResult />
    </ShowListResponse>
  </soap:Body>
</soap:Envelope>
SOAP 1.2
The following is a sample SOAP 1.2 request and response. The placeholders shown need to be replaced with actual values.

POST /com_InfoListAll.asmx HTTP/1.1
Host: www.testws.com
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <ShowList xmlns="http://tempuri.org/">
      <username>string</username>
      <password>string</password>
    </ShowList>
  </soap12:Body>
</soap12:Envelope>
HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <ShowListResponse xmlns="http://tempuri.org/">
      <ShowListResult />
    </ShowListResponse>
  </soap12:Body>
</soap12:Envelope>
HTTP GET
The following is a sample HTTP GET request and response. The placeholders shown need to be replaced with actual values.

GET /com_InfoListAll.asmx/ShowList?username=string&password=string HTTP/1.1
Host: www.testws.com
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<anyType xmlns="http://tempuri.org/" />
HTTP POST
The following is a sample HTTP POST request and response. The placeholders shown need to be replaced with actual values.

POST /com_InfoListAll.asmx/ShowList HTTP/1.1
Host: www.testws.com
Content-Type: application/x-www-form-urlencoded
Content-Length: length

username=string&password=string
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<anyType xmlns="http://tempuri.org/" />

How can you visualize each method It requests some parameters

The data source in Power BI to which I am connecting is Web and I do it as follows by adding the main URL of my Web Service in the basic section:

When trying to connect in this way, I get the following error message:

Also try from the advanced section adding the URL in parts with each of the methods as follows:

Here with this option if the connection is made but the data obtained is the same parameters and what is in the HTML.

I don't know if I have to make any previous configuration in the advanced section regarding the parameters, I hope someone can give me some guidance on how to do it.

[sebastianslz]

UPDATE 1:

I have tried connecting via blank query data source as follows:

And I have added the following query:

let
    Source = Web.Contents(
        "http://www.testws.com:84/com_InfoListAll.asmx",
        [
            RelativePath = "InfoTravel",
            Query=
            [
                username = "username",
                password = "password",
                Type = "99",
                ID = "1001"
            ]
        ])
in
    Source

When connecting to this data source I get the binary file as follows:

Then I click on it and it loads all the information in a single field of the table as follows:

I would like to know if there is a step prior to loading the data or I am loading it incorrectly.

[v-easonf-msft]

Hi, @sebastianslz 

Please check if these similar posts could help.

https://www.kizan.com/blog/consuming-xml-web-service-data-in-power-bi 

https://datachant.com/2017/03/30/web-scraping-power-bi-excel-power-query/ 

Best Regards,
Community Support Team _ Eason