Forum Discussion
Dynamic REST queries
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
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
Use the Text.Combine function in Power Query - e.g.
= Text.Combine(#"YOUR TABLE STEP"[id], ",")
This will give you a value you can add into your next step for the second lookup.
N.B. The "id" column needs to be data type Text for that to work.
Thank you!
But where do I add this?
Do I need to add a manual table or something, and store it there?
- I assumed you created the table with the "id" column via Power Query. Can you post a sample of the code you used to create the table?
Yea, so the Ids are in a table already, via power query. That is correctly understood.
What I don't understand, is where I add this combined string?
Do I create it as a parameter on the table with the ids?