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

Question

I am calling Azure Monitor Rest API via PowerQuery.&nbsp;I would like to make a fairly generic function which I can use for various different metrics.&nbsp;Some of the QueryString parameters passed to the API need to be optional. ("top" and "orderby" below)&nbsp;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.&nbsp;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.)&nbsp;How can I achieve my goal of only adding them to the Record if the parameters are not null?&nbsp;&nbsp;(ResourceUri as text, metricnames as text, aggregation as text, interval as text, timespan as text, optional top as text, optional orderby as text) =&gt;
let
    QueryParams =  [aggregation = aggregation, interval = interval, metricnames = metricnames, timespan=timespan],
    x = if orderby&lt;&gt;"" and orderby &lt;&gt; null then  Record.AddField(QueryParams, "orderby", orderby) else null,
    x2 = if top &lt;&gt;"" and top &lt;&gt; null then  Record.AddField(QueryParams, "top", top) else null,
    Source = Json.Document(Web.Contents(
            "https://management.azure.com/" &amp; 
            ResourceUri &amp; "/providers/microsoft.insights/metrics?api-version=2018-01-01&amp;%24filter=CollectionName%20eq%20%27*%27%20and%20DatabaseName%20eq%20%27*%27",
            [Query=QueryParams])),&nbsp;&nbsp;

martin__smith · Accepted Answer

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

Forum Discussion

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

2 Replies

Related content

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

Web.Contents with dynamic file URL

Dynamic subcription parameter not saving

Sum dynamic parameter fields multiple selected parameter

Web.Contents with complex Query parameter names