{"id":1146,"date":"2021-01-20T11:03:19","date_gmt":"2021-01-20T17:03:19","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=1146"},"modified":"2021-01-20T12:27:58","modified_gmt":"2021-01-20T18:27:58","slug":"web-contents-using-dynamic-and-duplicate-key-names-in-a-query","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2021\/01\/20\/web-contents-using-dynamic-and-duplicate-key-names-in-a-query\/","title":{"rendered":"Web.Contents: Using Dynamic and Duplicate key names in a Query"},"content":{"rendered":"\n\n\n

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

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

    I built a RelativePath<\/code> by Uri-escaping a list. <\/p>\n\n\n\n

    Building RelativePath<\/code> by Uri-escaping a list<\/h2>\n\n\n\n

    The final query will request the url: https:\/\/www.google.com\/search?q=dog&q=cat<\/a>
    This function generates the query string q=dog&q=cat<\/code> <\/p>\n\n\n\n

    This Input<\/td>"q", { "dog", "cat" }<\/code><\/td><\/tr>
    Will Return<\/td>q=dog&q=cat<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    let\n    QueryStr_UsingDuplicateKeys = (key as text, values as list) as text =>\n    \/\/ values are the 'value' of 'key'-> 'value' pairs\n        let\n            escapedList = List.Transform(\n                values,\n                each \n                    key & "=" & Uri.EscapeDataString( Text.From(_) )\n            ),    \n            joinedArgs = Text.Combine(escapedList, "&")\n        in\n            joinedArgs\nin\n    QueryStr_UsingDuplicateKeys<\/code><\/pre>\n\n\n\n
    Now you can use Web.Contents<\/code> as normal. <\/p>\n\n\n\n
    let\n    BaseUrl = "https:\/\/www.google.com",\n    queryStr = QueryStr_UsingDuplicateKeys(\n        "q", {"dog", "cat"}\n    ),\n    Options = [\n        RelativePath = "\/search?" & queryStr,\n        Headers = [ Accept="application\/json" ]\n    ],\n    response_binary = Web.Contents(BaseUrl, Options)\nin\n    response_binary<\/code><\/pre>\n\n\n\n
    Note: BaseUrl<\/code> is for the static part of the url. Everything else should be in options[RelativePath]<\/code> or options[Query]<\/code> See docs: Web.Contents <\/a>for details.<\/p>\n\n\n\n
    Easier Solution<\/h2>\n\n\n\n
    First try Chris’s method where you use Query<\/code>‘s Key-Value pairs a list<\/a>
    Results may vary<\/a>. If it does not work, you can try this method.
    I have not seen Query<\/code> publicly documented.<\/p>\n\n\n\n
    \n
    Query = [\n    q = {"dog", "cat"}\n]<\/code><\/pre>\n<\/div><\/div>\n\n\n\n
    Tips from Chris Webb<\/h2>\n\n\n\n
    Using Optional Parameters with Query<\/h3>\n\n\n\n
    \n
    Handling Multiple URL Query Parameters With The Same Name Using Web.Contents In Power Query\/Power BI<\/a><\/blockquote>