Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to 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
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.
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?
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?
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
54 | |
27 | |
15 | |
14 | |
13 |