Forum Discussion
Trying to use results from first query in second query on SOAP API
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.
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.
Responsive ResidentHi Anonymous
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.
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 Anonymous
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:
- a sample of the XML response (after you remove confidential data from it)
- the query
- the format of hardcoded org numbers