Calling multiple SOAP calls

[dramus]

I have a business need to grab data from a cloud source that requires multiple SOAP calls to complete.

The process is as follows:

1. Login
2. Execute Report
3. Download Report results
4. Logout.

Trying to impliment this through Power BI, Power Query and web.content(). Using Fiddler to diagnose what's going on I'm seeing multiple calls to each step, even though the PQ only calls each once. I've disabled "Parallel loading", "Detect Column Types", "Import relationships" and "Allow background refreshes".

Can anyone suggest a way in Power Query to limit the SOAP calls to a single request per call?

[sean_cochran]

Just curious if you ever found out a solution here - I have to go through a similar sequence to grab data from a soap API. Each of the steps works individually, but the sequencing is out of order and it's making multiple calls at one step.

[dramus]

I did, but it's "ugly" (i.e. outside of Power Query/Fabric). We use a logic app to do the heavy lifting of making all the calls to the SOAP API and then Power BI just calls the Logic app.

[sean_cochran]

I had a suspicion that's where this would lead. So far I've tried one query with all the steps in sequence, I've tried separate queries, and I've tried storing request outputs in buffered lists... no success so far. I saw some guy turning requests into custom functions and harcoding a time delay between them, but that feels too brittle for comfort. Thanks for the response!

[dramus]

No problem, I'll look in the ideas forum to see if there is anything there, but it would be nice if we could create atomic sections in power query, be able to say that "this section has to happen s a single unit and in this order". it would make these caall to web API's that require a stepped approach (e.g. Login, Request data, Fetch Data) call possible.

Here's the Idea: Microsoft Idea

[sean_cochran]

That would be really cool! Voted for the idea.

[smpa01]

@dramus Thanks for sharing this. There is one more thing I want to ask you.

The SOAP calls that you are making in this instance, you can make the same SOAP calls in browser and the responses are returned?

[smpa01]

@dramus  just being curious, how do you make SOAP calls through PQ? I could not do that and I had a use case? I had to switch to powershell making SOAP calls and fetching data.

[dramus]

SOAPEnvelope=
"<s:Envelope xmlns:s=""http://www.w3.org/2003/05/soap-envelope"" xmlns:a=""http://www.w3.org/2005/08/addressing""> 
<s:Header> 
<a:Action s:mustUnderstand=""1"">http://www.XXX.com/dataservices/bidata/DataService/ExecuteReport</a:Action>  
<a:To s:mustUnderstand=""1"">https://servicehost/services/DataService</a:To>  
</s:Header> 
<s:Body> 
<ExecuteReport xmlns=""http://www.XX.com/dataservices/bidata/""> 
<request xmlns:i=""http://www.w3.org/2001/XMLSchema-instance""> 
<ReportPath>"&ReportPath&"</ReportPath>  
<ReportParameters> 
</ReportParameters> 
</request> 
<context xmlns:i=""http://www.w3.org/2001/XMLSchema-instance""> 
<ServiceId>"&ServiceIDValue&"</ServiceId>  
<ClientAccessKey>"&MyKey&"</ClientAccessKey>  
<Token>"&TokenValue&"</Token>  
<Status>Ok</Status>  
<StatusMessage i:nil=""true"" />  
<InstanceKey>"&InstanceIDValue&"</InstanceKey>  
</context> 
</ExecuteReport> 
</s:Body> 
</s:Envelope> 
",
GetReportKey = Xml.Document(
    Web.Contents(
      "https://service2.XXX.com/services/DataService",
      [Content = Text.ToBinary(SOAPEnvelope), Headers = [#"Content-Type" = "application/soap+xml; charset=utf-8"]]
    )
  )

Here's an example from my PQ