I'm running SOAP calls to Salesforce Marketing Cloud inside Power Query.
In Salesforce marketing cloud we have more than alot 2,500 records, so for querying the data we need to use ContinueRequest as we have a 2,500 row limit batch coming back.
However when we run the query for the next batch of data, Power BI runs the query multiple times, which makes us miss those batches, as we can only call one batch only once.
Is there any way of stopping Power Query from doing this? Or the bigger question how exactly can I get the ContinueRequest to behave correctly.
So far I have disabled Type Detection, Background Data, Parallel loading, and set my Privacy Level to ignore, I'm also Buffering the tables,
so when referencing the tables they are not forced to re-run.
I also consolidated multiple steps together, to try to alleviate the problem, to no (noticeable) success.
I'm also only running one single query at once, and not referencing the output in any way (aside from buffering it)
In the test below I set up a filter which forces the data to be a little bit more than 2500 (4200), so both of the calls should only run once.
However even after the first call, we would run into "Error: No more data available in this batch" error.
function:
(ID as text) =>
let
url = "https://XXXXXX.soap.marketingcloudapis.com/Service.asmx",
SOAPEnvelope = "<s:Envelope xmlns:s=#(0022)http://www.w3.org/2003/05/soap-envelope#(0022) xmlns:a=#(0022)http://schemas.xmlsoap.org/ws/2004/08/addressing#(0022) xmlns:u=#(0022)http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd#(0022)>
<s:Header>
<Security xmlns=#(0022)http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd#(0022) >
<UsernameToken>
<Username>XXXXX</Username>
<Password>XXXXX</Password>
</UsernameToken>
</Security>
<a:Action s:mustUnderstand=#(0022)1#(0022)>Retrieve</a:Action>
<a:To s:mustUnderstand=#(0022)1#(0022)>https://XXXXXX.soap.marketingcloudapis.com/Service.asmx</a:To>
</s:Header>
<s:Body xmlns:xsi=#(0022)http://www.w3.org/2001/XMLSchema-instance#(0022) xmlns:xsd=#(0022)http://www.w3.org/2001/XMLSchema#(0022)>
<RetrieveRequestMsg xmlns=#(0022)http://exacttarget.com/wsdl/partnerAPI#(0022)>
<RetrieveRequest>
<ContinueRequest>" & ID & "</ContinueRequest>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>",
options = [#"Randomizer" = Number.ToText(Number.Random()),#"Randomizer2" = Number.ToText(Number.Random()),#"Randomizer3" = Number.ToText(Number.Random()),#"SOAPAction" = "Retrieve", #"Content-type" = "text/xml; charset=utf-8"],
Source = try Table.Buffer(Xml.Tables(Web.Contents(url,[Content=Text.ToBinary(SOAPEnvelope), Headers = options])){1}[Table]{0}[Table]{0}[Table]) otherwise null
in
#"Source"
call:
let
url = "https://XXXXXX.soap.marketingcloudapis.com/Service.asmx",
SOAPEnvelope = "<s:Envelope xmlns:s=#(0022)http://www.w3.org/2003/05/soap-envelope#(0022) xmlns:a=#(0022)http://schemas.xmlsoap.org/ws/2004/08/addressing#(0022) xmlns:u=#(0022)http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd#(0022)>
<s:Header>
<Security xmlns=#(0022)http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd#(0022) >
<UsernameToken>
<Username>XXXXX</Username>
<Password>XXXXX</Password>
</UsernameToken>
</Security>
<a:Action s:mustUnderstand=#(0022)1#(0022)>Retrieve</a:Action>
<a:To s:mustUnderstand=#(0022)1#(0022)>https://XXXXXX.soap.marketingcloudapis.com/Service.asmx</a:To>
</s:Header>
<s:Body xmlns:xsi=#(0022)http://www.w3.org/2001/XMLSchema-instance#(0022) xmlns:xsd=#(0022)http://www.w3.org/2001/XMLSchema#(0022)>
<RetrieveRequestMsg xmlns=#(0022)http://exacttarget.com/wsdl/partnerAPI#(0022)>
<RetrieveRequest>
<ObjectType>Subscriber</ObjectType>
<Properties>CreatedDate</Properties>
<Properties>Client.ID</Properties>
<Properties>EmailAddress</Properties>
<Properties>SubscriberKey</Properties>
<Properties>Status</Properties>
<Properties>UnsubscribedDate</Properties>
<Properties>EmailTypePreference</Properties>
<Filter xsi:type=#(0022)SimpleFilterPart#(0022)>
<Property>CreatedDate</Property>
<SimpleOperator>greaterThan</SimpleOperator>
<DateValue>2021-11-01</DateValue>
</Filter>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>",
options = [#"SOAPAction" = "Retrieve", #"Content-type" = "text/xml; charset=utf-8", #"Cache-Control" = "no-cache, no-store, must-revalidate"],
Source = Xml.Tables(Web.Contents(url,[Content=Text.ToBinary(SOAPEnvelope), Headers = options])){1}[Table]{0}[Table]{0}[Table],
#"Query Data" = List.Generate(() => [RESULT = SOAPCall(List.First(Source[RequestID]))], each _[RESULT][OverallStatus] = "MoreDataAvailable" , each [RESULT = SOAPCall(List.First(_[RESULT][RequestID]))], each _[RESULT])
in
#"Query Data"
Note: Randomizer was to try and stop PowerBI caching the query.
Any help or ideas would be much appricated.
