Report Bursting With Dataflows Gen2 Revisited: Action Sequences

A couple of weeks ago I wrote this post about how you can use the new Send Email action in Dataflows Gen2 to send out email-based reports to a list of recipients. I did it by creating a parameterised Dataflow – which worked but the end result was slow and therefore expensive. So I started thinking about other approaches and it turns out there is indeed another way of solving the problem, using a whole branch of M functions that is not properly documented and which I’ve never used before.

They key was understanding that the new Send Email functionality creates something called an action and that Ben Gribaudo wrote a very detailed post on this subject here and which you should probably read before continuing. Specifically, the section on the Action.Sequence function shows how you can compose multiple actions into a sequence. So for example here’s an example of the M code created by the Send Email action:

AlertAction.Send(
  MicrosoftOutlook.Actions(),
  [
    To      = {"chris@someemail.com"},
    Subject = "Email 1",
    Body    = "This is an email"
  ]
)

If you want to send two emails you can create a list of actions and pass it to Action.Sequence as follows:

let
  FirstEmail = AlertAction.Send(
    MicrosoftOutlook.Actions(),
    [
      To      = {"chris@someemail.com"},
      Subject = "Email 1",
      Body    = "This is the first email"
    ]
  ),
  SecondEmail = AlertAction.Send(
    MicrosoftOutlook.Actions(),
    [
      To      = {"helen@someemail.com"},
      Subject = "Email 2",
      Body    = "This is the second email"
    ]
  ),
  CreateSequence = Action.Sequence(
    {FirstEmail, SecondEmail}
  )
in
  CreateSequence

With this knowledge it was very easy to adapt the scenario from my previous post to send multiple emails without needing to parameterise.

So, given a query called Sales that returns the following table:

…I created a query that turns that table into a list of records using Table.ToRecords and then uses List.Transform to create a list of actions that send an email, one for each row, and then pass that list of actions to Action.Sequence:

let 
    TableRows = Table.ToRecords(Sales),
    EmailList = List.Transform(
        TableRows, 
        each AlertAction.Send(MicrosoftOutlook.Actions(), [
        To = {_[Email]}, 
        Subject = "Sales Report", 
        Body = "Hi "
                & _[Manager]
                & ", sales of "
                & _[Product]
                & " were "
                & Number.ToText(_[Sales])
        ]
        )
    ),
    CreateSequence = Action.Sequence(EmailList)
in 
    CreateSequence

Not only did this work and, when the Dataflow was refreshed, send out four emails identical to the the Dataflow in my previous post:

…but the Dataflow only took 11 seconds to run, which is a huge improvement over the 4 minutes 15 seconds that the parameterised version took:

So we’ve learned a cool new M trick and solved a familiar problem in a new way, which is fun. Is this safe to use in production? Not yet – I don’t fully understand the implications of using actions inside Dataflows so please don’t try this at home. But knowing that you can run your own actions from Dataflows opens up some interesting possibilities to explore in future blog posts.