Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])),
Solved! Go to 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])),
You could accept your reply as Solution to let other people find it easily!
Best Regards,
Community Support Team _ Jing
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])),