Dynamic REST queries

[Nicolai]

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?

[Anonymous]

Can you provide some sample data and what you are trying to achieve with it to help explain the issue ?

[Nicolai]

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

[Anonymous]

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.

[Nicolai]

Thank you!

But where do I add this?

Do I need to add a manual table or something, and store it there?

[Anonymous]

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?

[Nicolai]

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?

[Anonymous · Accepted solution]

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