Trying to use results from first query in second query on SOAP API

[chessTEA]

My SOAP API web service has two methods I'm trying to pull data from. The first method produces a list of six digit organization numbers. The second method produces directory information when fed one or more organization numbers. What I want to do is take the results from the first query and plug them into the second query. The goal is I want a query that gives me directory information for everybody without me having to hard code the organization numbers into that second query.

As far as I've gotten is getting the complete list of org numbers, condensing it into a single row/single column table with a thousand <string>012345</string> <string>012346</string> <string>012347</string> elements, converting that table into a list, creating a parameter based on that query, and plugging that "&AllOrgs&" parameter into the second query where I get an error:

Formula.Firewall: Query 'RetrieveDistricts' (step 'Removed Columns3') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

If I hard code all thousand org numbers into a text parameter, it runs fine. I read elsewhere to go in to File > Options > Privacy and select Always ignore Privacy Level settings, so I did that. Now I get this error instead when using my query parameter:

DataFormat.Error: Xml processing failed. Either the input is invalid or it isn't supported. (Internal error: The 'br' start tag on line 58 position 20 does not match the end tag of 'span'. Line 59, position 19.)

Details:    [Binary]

Anyone know where to go from here?

[chessTEA · Accepted solution]

Many months later, I have a solution. My first inclination to cram all the org numbers into a single parameter and run that through the second query was the wrong approach. What does work is running a query that creates a list of org numbers in a single column, one per row. Add a parameter for org number and use that in the second query that produces directory information. Convert the second query into a function, then finally Add Column > Invoke custom function in the list of org numbers. Also, privacy levels need to be set to "Always ignore Privacy level settings" in order for this to work. I think this is because Power Query is using the results of one query in another query.

I would've used a function the first time around had I known it were possible. I'm learning stuff every day here.

[BirkirGre]

Any chance you can share your approach (code)?

[ams1]

Hi @chessTEA 

You should post samples for the XMLs (after you removed confidential information), BUT I think the XML engine does not like the <br> tag, it likes the self closing <br/>.

I'm guessing you have <span> bla <br> bla </span> and it should be <span> bla <br/> bla </span>

So the engine sees an open span then an open br then a closed span - it cannot find the end of br.

One quick-and-dirty way of fixing is to convert the Binary XML to Text using Text.FromBinary, then to a text replacement of <br> with <br/> and then see if you get other errors.

Please mark this as answer if it helped.

[chessTEA]

Here's the query in question:

I don't see any <br> tags in that at all. Again, the thing runs, produces good results, if I use a hard coded parameter there in line 8. The errors pop up when I try to use a parameter based on the other query that gives me the org numbers. I've only been messing with this SOAP API in Power Query for a couple months, so I'm sure I'm missing some basic stuff here.

[ams1]

Hi @chessTEA 

Probably the <br> tag is in the response - and probably the response is a HTML formatted error.

To see the response in plain text and confirm there is a <br> there, you can change the ending of your query as follows:

...
    Source = Text.FromBinary(Web.Contents(url, [Content=Text.ToBinary(SOAPenvelope), Headers = options])))
in
    Source

If don't see the <BR>, then you can use the same Text.FromBinary method to see what the response of the AllOrgs query looks like.

Please mark helpful replies as answer.

[chessTEA]

I changed the query as suggested and I see what you mean. There are <br> tags all over it. It seems to be the entire OrgService.asmx webpage HTML code. Lots of this stuff, one per method:

<span>
<br>Return a collection of Organizations by type
</span>

I don't think I asked for that. Meanwhile, if I hard code the org numbers in the parameter, the query produces actual organizational data and no <br>s whatsoever. Odd.

[ams1]

Hi @chessTEA 

So given we've solved the <br> mistery, my proposal is to close this question - please mark as ANSWER the replies that helped AND if you want you can open a new question where you can request for help to "transform the XML response to another format" = the format of hardcoded org numbers:

1. a sample of the XML response (after you remove confidential data from it)
2. the query
3. the format of hardcoded org numbers