Tag: Example

Power BI Discord asked the question:
> How do you use duplicate keys, and dynamic urls with <a href="https://docs.microsoft.com/en-us/powerquery-m/web-contents">Web.Contents</a> ?
(They were using a web API that required duplicate keys)
After I wrote this, Chris Webb found an easier solution.

Requirements

1. You can’t use a regular record because keys must be distinct. Query = [ Key1 = 1, Key1 = 10] will throw an error.
2. You can’t put the **dynamic url** in the first argument Web.Contents or else refreshes can break

I built a RelativePath by Uri-escaping a list.

Building RelativePath by Uri-escaping a list

The final query will request the url: https://www.google.com/search?q=dog&q=cat
This function generates the query string q=dog&q=cat

let
    QueryStr_UsingDuplicateKeys = (key as text, values as list) as text =>
    // values are the 'value' of 'key'-> 'value' pairs
        let
            escapedList = List.Transform(
                values,
                each 
                    key & "=" & Uri.EscapeDataString( Text.From(_) )
            ),    
            joinedArgs = Text.Combine(escapedList, "&")
        in
            joinedArgs
in
    QueryStr_UsingDuplicateKeys

Now you can use Web.Contents as normal.

let
    BaseUrl = "https://www.google.com",
    queryStr = QueryStr_UsingDuplicateKeys(
        "q", {"dog", "cat"}
    ),
    Options = [
        RelativePath = "/search?" & queryStr,
        Headers = [ Accept="application/json" ]
    ],
    response_binary = Web.Contents(BaseUrl, Options)
in
    response_binary

Note: BaseUrl is for the static part of the url. Everything else should be in options[RelativePath] or options[Query] See docs: Web.Contents for details.

Easier Solution

First try Chris’s method where you use Query‘s Key-Value pairs a list
Results may vary. If it does not work, you can try this method.
I have not seen Query publicly documented.

Query = [
    q = {"dog", "cat"}
]

Tips from Chris Webb

Using Optional Parameters with Query

Web.Contents(
    "http://jsonplaceholder.typicode.com/comments",
    [Query = [postId = {}] ]
)

Which results in the url: <a href="http://jsonplaceholder.typicode.com/comments">http://jsonplaceholder.typicode.com/comments</a>
This is a good place to use Power Query‘s null coalesce operator (Which isn’t in the official docs)

Query = [postId = myPostId ?? {}]

Capturing HTTP Requests without Fiddler