Microsoft Fabric: pass dynamic number of elements from notebook to Outlook365 activity

Question

In Microsoft Fabric I have a notebook activity with a dataframe with some scraped data.

My goal is to perform some aggregations and pass them to outlook365 activity, where I can format incoming aggregated values the way I need.

I need some help with high level overview of how I can achieve this.

I found the simplest way to pass "static" aggregations (sum or average of a column) via mssparkutils.notebook.exit:

import json

summ = 250
average = 100

result = {
    'sum': summa,
    'snitt': snitt,
}

result_json = json.dumps(result)

mssparkutils.notebook.exit(result_json)

and then capture every value of it in outlook activity with

 @concat('value is ', json(activity('exit').output.result.exitValue).summ) OR @concat('value is ', json(activity('exit').output.result.exitValue).average)

The data is scraped on hourly basis, which means that the length of the dataframe will always be different. Practically, I need to capture values of two columns and store them into a variable, suppose something like this, which I can easily do in notebook:

dynamic_vars = [['var1', 7], ['var2', 8]]

The tricky part, and what I need help with comes from the dynamic nature of the dataframe - sometimes the length of this list can be 0, so that dynamic vars is an empty list, other times it can hold a few items.

I tried something like this:

import json

summ = 250
average = 100
dynamic_vars = [['var1', 7], ['var2', 8]]

result = {
    'sum': summa,
    'snitt': snitt,
    'dynamic_vars': dynamic_vars
}

result_json = json.dumps(result)

mssparkutils.notebook.exit(result_json)

and then use if conditions activity together with pipelike variable (string) for determine what will be passed further to outlook activity.

Inside the if conditions activity, if the dynamic_vars list is not empty, then

@join(json(activity('exit').output.result.exitValue)['late'], ', <br>')

will be passed to outlook activity, where I capture it with variables('dynamic_vars'). The result I'm getting is:

['var1', 7]
['var2', 8]

But my desired format is something like:

var1: 7
var2: 8

So my question is two folded:

• I somehow feel that they way I implemented this is may be not the best on a high level. Is there a better way to implement this on a high level?
• If I would keep this method, where I exit notebook with aggregated values in a dict/json, how can I "slice" this structure the way I need inside outlook activity?

Big thanks in advance!

Answer 1

You can convert the array value to string and then format the output value as per your requirement. Below is the approach.

• I have stored the dynamic_vars value in a variable dynamic_vars using the below expression @json(activity('exit').output.result.exitValue).dynamic_vars.

• This will give the value in array format. To convert this into the expected format, convert them to string and then replace the comma with : and remove the array brackets. Expression will look like below.

@replace(replace(replace(replace(string(variables('dynamic_vars')),',',':'),']:[','
'),'[',''),']','')