Forum Discussion
Dynamic REST queries
So, I've been googling around a bit and found a lot of references to dynamic REST queries being possible, but unfortunately not enough to get me started with it.
So, I have a query that returns a table where one column is IDs.
I would like to concatenate all these IDs into one string, and use them in another query.
But where should I actually do this? Where can i create this "parameter" to insert into my next query?
See the sample code below which may help explain:
let // Create sample data table Source = #table({"id", "Name"}, {{1,"Horse"}, {56, "Cow"}, {3543, "Sheep"}, {8777, "Dog"}, {23433, "Pig"}, {45454, "Cat"}}), //Ensure the "id" is Text for the next Text.Combine step ChangedType = Table.TransformColumnTypes(Source,{{"id", type text}, {"Name", type text}}), //Combine the id's into a CSV value CombinedIDs = Text.Combine(ChangedType[id], ","), //Create your URL including the CSV list of IDs url = "http://yourtargetsite.com?id=" & CombinedIDs, //Get JSON from your target site Json = Web.Contents(url), //Process the returned results as neeeded - e.g. FormattedAsJson = Json.Document(GetJson) in FormattedAsJson
7 Replies
I can try to explain a bit more.
I call a REST url that provides me with a list of objects. Transfered into a table, there's one column I'd like to use. It's called Id. So there's maybe 10 entries (varies of course) with ints in the Id column.
I would like to grab all these Ids, combine them into a comma seperated string, and insert them into a query towards another REST call.
These REST services are NOT oData.
So I get something like: http://whatever/events?ItemId=<myString>
So let's say my first call (to another endpoint) returns the following table.
Then I'd like to use those Id's to form the following url: http://whatever/events?ItemId=1,56,3453,8777,23433,45454
