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
juliovidigal's avatar
juliovidigal
Icon for Advocate I rankAdvocate I
9 years ago

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



JasonL's avatar
JasonL
Icon for Advocate II rankAdvocate II
9 years ago

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>

  • juliovidigal's avatar
    juliovidigal
    Icon for Advocate I rankAdvocate I
    9 years ago

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

    Tanks

  • juliovidigal's avatar
    juliovidigal
    Icon for Advocate I rankAdvocate I
    9 years ago

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

    • hugoberry's avatar
      hugoberry
      Icon for Responsive Resident rankResponsive Resident
      9 years ago

      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.

      • juliovidigal's avatar
        juliovidigal
        Icon for Advocate I rankAdvocate I
        9 years ago

        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>[email protected]</username>

                 <password>aaaaaaaa</password>

              </clientLogin>

           </soapenv:Body>

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

        in

            Source