cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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

The advanced query editor opens the door to pretty much anything when you understand how it works and how Power Bi works (I mean by that all the mecanismes that are under the "hood")

Highlighted
Advocate I
Advocate I

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

I Try.

https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL

 

XML saved  in c:\a


<soapenv:Envelope xmlns:v20="https://advertising.criteo.com/API/v201305" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
<v20:clientLogin>
<!--Optional:-->
<v20:username>XXXXXX</v20:username>
<!--Optional:-->
<v20:password>XXXXX</v20:password>
<!--Optional:-->
</v20:clientLogin>
</soapenv:Body>
</soapenv:Envelope>

Advanced Editor

let

Source = Xml.Tables(Web.Contents("https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL"
,[Content=File.Contents("C:\a\a.xml"),Headers=[#"Accept-Encoding"="gzip,deflate", SOAPAction="", #"Content-Type"="text/xml;charset=UTF-8"]]))
in
Source


Return this error..

 

DataSource.Error: Web.Contents failed to get contents from 'https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL' (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=https://advertising.criteo.com/API/v201305/AdvertiserService.asmx
Url=https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL

 

Can you help me?

 

Tanks



Highlighted
Advocate II
Advocate II

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

Have you tried to connecte to your web service with something else than Power Bi to see if it works?

I can test it from my office, proxy block advertisement website

Also are you sure your webservice returns XML?

Highlighted
Advocate II
Advocate II

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

Your M code seems ok, the only difference I see with me is in your XML

you use <v20:clientLogin>

 

were i use </web:returnData>

Highlighted
Advocate I
Advocate I

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

Yes, it is working, I have tested using SoapUI, and I have had the correct return in XML.
This is the return in SoapUI...!

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<clientLoginResponse xmlns="https://advertising.criteo.com/API/v201305">
<clientLoginResult>XXXXXXXXXXXXX</clientLoginResult>
</clientLoginResponse>
</soap:Body>
</soap:Envelope>

Highlighted
Advocate I
Advocate I

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

I'm going to do a test using </ web: returnData> ...

Tanks

Highlighted
Advocate I
Advocate I

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

I've removed the "SOAPAction =" "and now it's working.
Thank you

Highlighted
Responsive Resident
Responsive Resident

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

It seems that making SOAP more user firendly for PowerBI is because of lack of Xml create functions.

Has anyone tried to build an populate an Xml.Document type as per the MSDN document? https://msdn.microsoft.com/en-us/library/mt260907.aspx

Maybe if you start from there you can resolve a lot of SOAP comunication issues.

Highlighted
Advocate I
Advocate I

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

Actually, the next step of my project was this, because I'm going to publish this and I do not find it interesting to keep calling files to make the call.
So now I've put the XML inside PowerQuery.

Because I'm going to use the token return on other calls, it's unfeasible that it stays on file.

It looks like this:

let

    Source = Xml.Tables(Web.Contents("https://advertising.criteo.com/API/v201305/AdvertiserService.asmx?WSDL"

    ,[Content=Text.ToBinary("<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https://advertising.criteo.com/API/v201305"&Character.FromNumber(34)&" xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&">

   <soapenv:Body>

      <clientLogin>

         <username>aaaaa@aaaa.com</username>

         <password>aaaaaaaa</password>

      </clientLogin>

   </soapenv:Body>

</soapenv:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Content-Type"="text/xml;charset=UTF-8"]]))

in

    Source

Highlighted
Responsive Resident
Responsive Resident

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

Might want to consider using single quotes in your xml. Or if you need those double quoutes try using #(0022) in your query it is equivalent to Character.FromNumber(34), plus you don't need to concatenate them.

instead of

"<soapenv:Envelope xmlns="&Character.FromNumber(34)&"https"

try

"<soapenv:Envelope xmlns=#(0022)https"

 

Plus, have you considered using Text.Format to keep password and username out of the XML qurey builder?

Helpful resources

Announcements
Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors