Simple Report Bursting With Fabric Dataflows Gen2

When the new Send Email action was announced for Fabric Dataflows Gen2 – which allows you to send an email from a dataflow when it refreshes – I was curious to know if you could use it to send multiple emails to multiple different people, and therefore do report bursting or something like it. There are already several different ways to do this in Power BI/Fabric, for example using Power BI report subscriptions or paginated reports, but there might some cases where using a dataflow is easier or more convenient. It turns out that is is possible but only in combination with another relatively new feature: Parameterised Dataflows.

Here’s a simple example. I created a Dataflow Gen2 with a query called Sales that returned the following source data (the email addresses in the Email column have been obscured):

I then created a text parameter in the dataflow called EmailAddress and used it to filter the Sales query so it only returned one row, the row where the value in the Email column matched the value in the EmailAddress parameter. Then I clicked the new Send Email button to create an email destination and edited the resulting M code so it looked like this:

AlertAction.Send(
  MicrosoftOutlook.Actions(), 
  [
    To = {EmailAddress}, 
    Subject = "Sales Report", 
    Body = "Hi "
      & Sales{0}[Manager]
      & ", sales of "
      & Sales{0}[Product]
      & " were "
      & Number.ToText(Sales{0}[Sales])
  ]
)

Note that the To line for the destination is the EmailAddress parameter and the Body is a simple M expression that assumes the Sales query returns a table with a single row in it and which generates the contents of the email body.

Here’s what the Dataflow diagram looked like:

I then made sure the “Enable parameters to be discovered and overriden for execution” option was enabled in the Options dialog:

…and saved the dataflow without running it.

Next I created a new Fabric Pipeline with a ForEach activity in it. In the Settings dialog I checked the Sequential box and, being a bit lazy, in Items I used an expression something like this to create an array of all the email addresses from the Sales query in my dataflow:

@createarray('name1@domain1.com','name2@domain2.co.uk')

Then I added a Dataflow activity inside the ForEach activity, pointed it to my dataflow and passed the current value from the ForEach loop (using the expression @item()) into the EmailAddress parameter of my dataflow.

Finally, I ran the Pipeline and checked the inbox of one of the email addresses present in the Sales query and found the following email:

So it worked. Is this a good idea for sending out a large number of emails? Probably not given that it was fairly slow and failed if I tried to introduce any concurrency into the ForEach activity:

But if you only need to send out a few emails and you prefer to use Power Query it’s quite convenient. And when the ability to use HTML formatting in the email body comes (as promised on the Fabric roadmap here, which I guess will be very similar to the new Data Visuals feature) then you will be able to use this technique to send out quite complex reports.