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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors