Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Nicolai
Frequent Visitor

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

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.

 

table.png

 

 

 

 

 

 

 

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
Not applicable

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?

Anonymous
Not applicable

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?

Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.