Forum Discussion
Helper IWeb.Contents Query parameter - multiple filed values for one parameter
Hi I am trying to specify multiple filed values for one parameter. I had a look at a similar post.
However, it works well for a single field value(eg., Test1). When I spesified multiple values it doesn't work(e.g., {"Test1","Test2"}).
Below is my code. Can anyone help me? Thanks.
= Json.Document(Web.Contents("http://api.test.org/v3/records.json",[
Query=[#"and[display_collection][]"="Test",
#"sort"="date",
#"direction"="desc",
#"and[subject][]"={"Test1","Test2"},#"APIToken"="123"]
]))
The below code worked for me.
= let // List of subjects subjects = { "TEST1", "TEST2" }, // Function to fetch data for a single subject getDataForSubject = (subject as text) => let queryParams = [ #"per_page" = "100", #"and[display_collection][]" = "test", #"sort" = "date", #"direction" = "desc", #"and[subject][]" = subject, #"APIToken" = "123" ], source = Json.Document(Web.Contents("http://api.test.org/v1/records.json?", [Query = queryParams])) in source, subjectQuery = List.Transform(subjects, each getDataForSubject(_)) in subjectQuery
9 Replies
- lbendlin
Super User
is the "and" really part of the specification?
The usual approach for multiple values is to specify the query parameter multiple times
...&subject=Test1&subject=Test2&APIToken=123
luckygirlThanks, it doesn't work. "The name 'and[subject][]' is defined more than once". According to this blog post Chris Webb's BI Blog: Handling Multiple URL Query Parameters With The Same Name Using Web.Contents In Power Query/Power BI it should work. Can't figure out the reason.
- danextian
What string does the API expect if subject parameter has multile values? What is the full url?
- luckygirl
Hi, Thnank you so much. This is very helpful. However, so far from the experiments that I have done, it is because of the "and" in and "and[subject][]" causes thie issues. Still, the url looks like this.
- luckygirl
Apologies, the full URL is this. Thanks.
v-saisrao-msftCommunity Support
Hi luckygirl,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
- v-saisrao-msft
Hi luckygirl,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Thanks for the detailed post and for referencing Chris Webb’s blog—it’s a very helpful source when dealing with APIs that require repeated query parameters.
You're right in observing that, Web. Contents in Power Query don't allow duplicate field names in the Query record. So, while APIs often expect multiple values to be passed like this Power Query throws an error if you try to define "and[subject][]" more than once in the Query record.
let baseUrl = "http://api.test.org/v3/records.json?", subjects = {"Test1", "Test2"}, subjectQuery = Text.Combine( List.Transform(subjects, each "and[subject][]=" & Uri.EscapeDataString(_)), "&" ), otherParams = "and[display_collection][]=Test&sort=date&direction=desc&APIToken=123", fullUrl = baseUrl & subjectQuery & "&" & otherParams, response = Json.Document(Web.Contents(fullUrl)) in responseIf this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
