How can I dynamically add Query parameters in Web.Contents call?

[martin__smith]

I am calling Azure Monitor Rest API via PowerQuery.

I would like to make a fairly generic function which I can use for various different metrics.

Some of the QueryString parameters passed to the API need to be optional. ("top" and "orderby" below)

Azure monitor throws an error if they are passed as empty string so they need to be either passed with a value or omitted entirely.

I found there is a function "Record.AddField" but I don't see how I can add that in conditional logic to just call this when needed. I had thought I had found a hacky workaround that appeared to work in initial testing (outside of a function) but no longer works when inside a function (I assume the assignments to x1 and x2 are just skipped as they don't affect anything so I can't get the side effect.)

How can I achieve my goal of only adding them to the Record if the parameters are not null?

(ResourceUri as text, metricnames as text, aggregation as text, interval as text, timespan as text, optional top as text, optional orderby as text) =>
let
    QueryParams =  [aggregation = aggregation, interval = interval, metricnames = metricnames, timespan=timespan],
    x = if orderby<>"" and orderby <> null then  Record.AddField(QueryParams, "orderby", orderby) else null,
    x2 = if top <>"" and top <> null then  Record.AddField(QueryParams, "top", top) else null,
    Source = Json.Document(Web.Contents(
            "https://management.azure.com/" & 
            ResourceUri & "/providers/microsoft.insights/metrics?api-version=2018-01-01&%24filter=CollectionName%20eq%20%27*%27%20and%20DatabaseName%20eq%20%27*%27",
            [Query=QueryParams])),

[v-jingzhang]

Hi @martin__smith 

You could accept your reply as Solution to let other people find it easily!

Best Regards,
Community Support Team _ Jing

[martin__smith · Accepted solution]

Ah figured it out

(ResourceUri as text, metricnames as text, aggregation as text, interval as text, timespan as text, optional top as text, optional orderby as text) =>
let
    QueryParams =  Record.Combine({
    [aggregation = aggregation, interval = interval, metricnames = metricnames, timespan=timespan],
    if orderby<>"" and orderby <> null then  [orderby = orderby] else [],
    if top<>"" and top <> null then  [top = top] else []
}),
    Source = Json.Document(Web.Contents(
            "https://management.azure.com/" & 
            ResourceUri & "/providers/microsoft.insights/metrics?api-version=2018-01-01&%24filter=CollectionName%20eq%20%27*%27%20and%20DatabaseName%20eq%20%27*%27",
            [Query=QueryParams])),