M · Power BI · Power Query

Handling Multiple URL Query Parameters With The Same Name Using Web.Contents In Power Query/Power BI

Some time ago I wrote a pair of popular posts about using the Query and RelativePath options of the Web.Contents function in Power Query and why they are important for dataset refresh. I have recently learned something extra about this subject which merits a new post, though: how to handle multiple URL query parameters with the same name.

In the examples for this post I’ll be using a free, fake web service available at http://jsonplaceholder.typicode.com/ which requires no authentication so you will be able to run the code I show yourself. Let’s start by considering the following call to the comments resource of this API:


In M, you can use the Web.Contents function and the Query option to call the API like so:

//Generates the URL http://jsonplaceholder.typicode.com/comments?postId=1

Now this API – unlike many others – allows you to pass multiple URL query parameters of the same name. For example this is a valid call:


The problem is, if you try to do the following in M:


…you’ll get an error, because the Query option take a record and a record can’t have two fields with the same name.

Here’s where it gets interesting. In the working example above, the postId field in the Query record contains a text value. The field can contain a list of text values instead, though, and this is how you solve the problem:

//Generates the URL http://jsonplaceholder.typicode.com/comments?postId=1&postId=2

This results in a URL with two query parameters that are both called postId but have the values 1 and 2 respectively.

There’s something else worth mentioning about this too. Sometimes when you’re generating a URL you may not want to add a query parameter to it if a value is null. One way of handling this is to start with an empty record and then add fields to it using the Record.AddField function, but using an empty list provides another approach. For example:

//Generates the URL http://jsonplaceholder.typicode.com/comments
//and *not* http://jsonplaceholder.typicode.com/comments?postId=

…generates a URL without the postId query parameter in. This means you can write a simple function with an optional parameter for postId like this:

(optional myPostId as text) =>
    Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId=myPostId ?? {}]])

If a text value is passed to myPostId then the postId query parameter is added to the URL; if no value is passed, myPostId is null and the ?? null coalescing operator (see Ben Gribaudo’s post here for more information on this operator) can be used to replace it with an empty list.

One last thing to mention: if you need to see the web service calls generated by Web.Contents when you’re testing in the Power Query Editor, you can use the query diagnostics functionality as I show here.

[I’m extremely grateful to Dave Maiden for letting me know about all this – it answers a question I have been asked several times in the past]

12 thoughts on “Handling Multiple URL Query Parameters With The Same Name Using Web.Contents In Power Query/Power BI

  1. That’s neat. I solved it by generating `options[RelativePath]` using [Uri.EscapeDataString](https://docs.microsoft.com/en-us/powerquery-m/uri-escapedatastring)


    Have you seen docs or information on how `Query` behaves for this function or other data sources, like parameterized SQL queries? The [pingback](https://curatedsql.com/2021/01/11/handling-duplicate-query-string-values-with-power-query/) says he found different results depending on the environment.

  2. Hi Chris, thank you for this information! I am trying to replicate the RelativePath soultion to my dynamic data source which uses the following REST API:
    Json.Document(Web.Contents(“https://api-au.myconnectwise.net/v4_6_release/apis/3.0/sales/opportunities?pagesize=1000&conditions=locationId in (32)”, [Headers=[Authorization=”Basic Sxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==”, Clientid=”dxxxxxxxxxxxxxxxxxxxxxxxxxxxx0″]]))

    This gives me a list which I further transform into tables. Not sure how to get the condition part in Query [].
    I have come to following reformatting so far:
    Source = Json.Document(
    RelativePath = “v4_6_release/apis/3.0/sales/opportunities”,
    Query = [pagesize = 1000
    & locationId = {“32”}]
    Headers = [
    Authorization = “Basic Sxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==”,
    Clientid = “dxxxxxxxxxxxxxxxxxxxxxxxxxxxx0”

    Error returned:
    “Expression.Error: The name ‘locationId’ wasn’t recognized. Make sure it’s spelled correctly.”

    Could really use your help as I am just stepping in the M query universe!
    Thank you!

  3. Thank you so much for the info! I have a question. How can you solve a in the relative path, a query parameter that incluye a M function:

    This is the normal url –> Source = Json.Document(Web.Contents(“https://mycompany.pipedrive.com/v1/notes?limit=500&pinned_to_person_flag=0&api_token=12345abcde&start=”&Number.ToText(Page))),

    This is the relative path source that is not working (because of the NumberToText) –> Source = Json.Document(Web.Contents(“https://mycompany.pipedrive.com/v1/notes”, [Query=[api_token=”03a31747879be86fe4350e0aa439b7c231396109″, limit=”500″, pinned_to_person_flag=”0″, start=”Number.ToText(Page)]])),

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      It looks like you need to remove the double quote immediately before Number.ToText

  4. Hi I am trying to populate azure application Insights log data to power bi using API key.
    But I am not able to schedule refresh when I publish the report to the portal.
    Error says : You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:

    The Query I am using looks as follows :

    let AnalyticsQuery =
    let Source = Json.Document(Web.Contents(“https://api.applicationinsights.io/v1/apps//query”,
    | where timestamp > ago(12h)
    | project timestamp, operation_Id
    | join (dependencies
    | where timestamp > ago(1d)
    | summarize sum(duration) , count(itemCount) by operation_Id, type)
    on operation_Id
    Headers=[#”x-api-key”=””, #”Prefer”=”response-v1=true”]])),
    TypeMap = #table(
    { “AnalyticsTypes”, “Type” },
    { “string”, Text.Type },
    { “int”, Int32.Type },
    { “long”, Int64.Type },
    { “real”, Double.Type },
    { “timespan”, Duration.Type },
    { “datetime”, DateTimeZone.Type },
    { “bool”, Logical.Type },
    { “guid”, Text.Type },
    { “dynamic”, Text.Type }
    DataTable = Source[tables]{0},
    Columns = Table.FromRecords(DataTable[columns]),
    ColumnsWithType = Table.Join(Columns, {“type”}, TypeMap , {“AnalyticsTypes”}),
    Rows = Table.FromRows(DataTable[rows], Columns[name]),
    Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
    #”Changed Type” = Table.TransformColumnTypes(AnalyticsQuery,{{“timestamp”, type datetime}}),
    #”Added Custom” = Table.AddColumn(#”Changed Type”, “Rounded_timestamp”, each Number.Round(Number.From([timestamp]) * (60 * 24 /60),0) / (60 * 24 /60)),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Added Custom”,{{“Rounded_timestamp”, type datetime}})
    in #”Changed Type1″

    Appreciate your help in resolving this issue
    Thanks Saket

  5. Hi, I am having this issue, I am using shared drive to pick up my excel file, everything works fine but when I publish to the service I get the error
    This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

  6. Hello Chris, I really need your help.

    The code below is before authorization with token, but now our company passed to authorization with token:
    = Table.AddColumn(#”Renamed Columns”, “Data”, each try Json.Document(Web.Contents(“https://issuetracking.com/rest/api/2/issue/”,
    [RelativePath= [key] & “?expand=changelog&maxResults=999”])) otherwise null)

    Here is how we normally access with token:
    = Json.Document(Web.Contents(“https://issuetracking.sdd.com/rest/api/2/search?jql=&maxResults=2000,[Headers=[Authorization=”Bearer NzQ4NzcwMjk2NTM5OsK58HIugv+tY3Y”]]))

    But now I dont know how to access by token with also relative path. I tried this (double authorization) but data column is empty:
    = Table.AddColumn(#”Renamed Columns”, “Data”, each try Json.Document(Web.Contents(“https://issuetracking.com/rest/api/2/issue/”,
    [Headers=[Authorization=”Bearer NzQ4NzcwMjk2NTM5OsK58HIugv+tY3YCw”]],
    [RelativePath= [key] & “?expand=changelog&maxResults=999″,
    Headers=[Authorization=”Bearer NzQ4NzcwMjk2NTM5OsK58HIugv+tY3YCw”]])) otherwise null)

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.