Web.Contents Query parameter - multiple filed values for one parameter

[luckygirl]

Hi I am trying to specify multiple filed values for one parameter. I had a look at a similar post. 

https://community.fabric.microsoft.com/t5/Desktop/Web-Contents-Query-parameter-multiple-parameters-w... 

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"]
 ]))

[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
    response

If 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. 

[danextian]

What string does the API expect if subject parameter has multile values? What is the full url?

[luckygirl]

Apologies, the full URL is this. Thanks.

[v-saisrao-msft]

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.

[luckygirl · Accepted solution]

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

[lbendlin]

you should not include the question mark in the 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.

[lbendlin]

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

[luckygirl]

Thanks, 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 I... it should work. Can't figure out the reason.