E-mail the selected record in #powerbi with #powerapps and #flow and include a CSV File with the records

One of my clients called me the other day and asked whether it was possible to export the selected order that was selected in the current report page – as she wanted to send the information to another user. I explained the export data feature from the visual action menu but she didn’t want to download a file and then locate that and then switch to Outlook and click new mail – type the correct the e-mail and attach the file – that was not very Power like – to much clicky clicky – because all the data was actually available when she had filtered the report for that particular record – the e-mail she wanted to mail the data to and off course the data she saw on the screen.

Hmm… Let’s see how we can use the PowerPlatform stack to solve this requirement.

A Power BI report to use a the demo

Based on the AdventureWorks database I build a report to select a Customer Key and filter it by a specific SalesOrderNumber

So, when the user has selected a sales order we should be able to send the information about the current sales order to either the customer or a alternate e-mail address.

PowerApps to works

Well we do have visual to integrate a PowerApp in our report and even though its in preview and there are some hickup’s we can make it work

But in order to insert it we do need to do this in a browser so let’s publish the report.

After I publish the report to the service, we can switch to edit mode and insert the PowerApp visual in the service.

This will introduce to a start screen with instructions on how to get started.

The important part is to include the fields from the datamodel you want to have access to in the PowerApp and this is done by adding the fields to the “PowerApps data” sink in the visuals pane.

As soon as you add the first field the PowerApp visual will change and let you select either to choose and app or create a new app

I added all these fields as I want to be able to access them in PowerApps

You have to use either Chrome or Edge browser to do this and keep calm – you will experience difficulties selecting the PowerApp visual with the mouse if you deselect it – try using the tab to rotate through the visual selection instead.

When all the fields is added we are ready to create a new app – well depending on the browser behaviour – so if it doesn’t work in Edge try in Chrome 🙂 – so click new app and a new tab with PowerApps will appear

Notice that a Gallery control is inserted and its linked to “‘PowerBIIntegration’.Data” – which is the filtered records in PowerBI –

If I modify the Gallery Title field to show SalesOrderNumber instead – you can see the magic

As is selected in PowerBI

Let’s modify the PowerApp

We don’t need the gallery control – so I delete this and insert a couple of other controls

So, I insert

  • Label to show sales order number
  • Text box to enter an email address
  • Check box to select whether to include a csv file
  • A mail icon for mail using office365
  • A mail icon for mail using flow and include the csv file
  • HTML text control to construct the mail body

One of the first thing I get trapped in is when showing the SalesOrderNumber in the label control.

The PowerBIntegration object is a table with records so you have to refer to a specific record/row to get the information from the field.

Therefore, use the FILTER function or in this case the FIRST function to get the value you need.

Let’s construct a message body

We can do this by constructing a HTML text with this formula

“Dear ” & First([@PowerBIIntegration].Data).EmailAddress & “<br><br>” &

“<b> Sales Order Number: </b>” & First([@PowerBIIntegration].Data).SalesOrderNumber & “<br>”

& “Products: “

& “<table width=’100%’ border=’1′ cellpadding=’5′ style=’border:1px solid black; border-collapse:collapse’>” &

“<tr style=’background-color:#efefef’>

<th>Model</th> <th> Sales Amount </th>

</tr><tr>” &

Concat(PowerBIIntegration.Data, “<td>” & ModelName & “</td> ” & “<td>” & ‘Sales Amount’ & “</td>”, “</tr><tr>”)

& “</table>”

This will give us this table in the HTMLText control – you can expand the table to your needs.

Let’s send an email

In order to send an email without a CSV file we can use the built in Office 365 Outlook datasource via View and Data sources

After adding this we can add a formula to the OnSelect property of the mail icon.

Test the function by clicking the run button

And hit the envelope – and

We get a notification

And after a while an email

Does it work from PowerBI – yes

You have to give the app an name and save it (and publish) – and switch back to your Power BI report

And your PowerApp visual will now show the newly created app in the visual – and when we select another customer and sales order – we see the app values changes.

And when clicking the send mail icon, we get a notification and an email.

Now the tricky part – include a CSV file

In order to do this, we need help from another member of the PowerPlatform family – Microsoft Flow – this is because we cannot include files or create files using the Office 365 Outlook connector.

This can all be done using the flow – and this is the part that took me the longest time to solve.

First let’s connect a flow to the second envelope on the PowerApp screen –

You can either create a new flow or use an existing.

As this blog post is a bit long, I will show you how the final flow looks like and take you through the steps/actions in the “CreateCSVFile” flow

When I add the flow, you see that the run statement has 4 arguments

These are added in the flow via the “Ask in PowerApps” in the different steps.

Let’s look at the flow

The flow has 5 steps

The first step “PowerApps” has no settings but builds the connection to the PowerApp “click”

Next step is creating a variable called csvdataasjson.

And the argument value contains a string value that is provided by the call from PowerApps.

This will actually be a JSON string containing the records we want in our CSV file.

Next step “Parse JSON” takes the value of the variable and uses the dataoperation Parse JSON to parse the JSON string into an object

Remember to update the Schema if you change what you send to the flow via PowerApps.

The next step “Create CSV table” takes the output from “Parse JSON” and can convert the JSON to a CSV table

I have used the advanced options to show the headers.

The final step uses the “Send an email (V2)” action to send and a email

The To, Subject and Body argument uses the “Ask in PowerApps” to create arguments we can use when we run the flow in PowerApps

Call the flow from PowerApps with arguments

In PowerApp we can now activate the flow with arguments from the data using the OnSelect action on the second Mail icon

Notice the arguments the Run statement matches the PowerApps variable names in the Flow.

The initialize_value contains the a formula to construct a JSON string – if you need to include more fields from the PowerBI Dataset you simply extend the string construction.

If you check a flow run you can see the result in the Value box

Now save and republish the app and lets see if it works via PowerBI.

Let’s mail a CSV file

– A tip when moving back and forth from PowerApps to PowerBI – switch between report pages to refesh the PowerApp visual – this updates the app better than refresh of the page.

When clicking the second envelope we will activate the flow via PowerApps

And after a few seconds I receive an email

Including a CSV file with the Orderlines

To summarize

By using the power of each element of the PowerPlatform we can send an email with the selected record/s in PowerBI – but it might be a bit advanced for the citizen developer.

  • Power BI to find the data
  • PowerApps to mail a simple e-mail with the information
  • Flow to include a CSV file in the mail

Please vote for this idea if you want to make it easier to create the JSON string in PowerApps – https://powerusers.microsoft.com/t5/PowerApps-Ideas/Add-JSON-stringify-or-something-similar-for-debugging/idc-p/268352#M25884

And let me know if you have questions or feedback to my method – POWER ON !

17 thoughts on “E-mail the selected record in #powerbi with #powerapps and #flow and include a CSV File with the records

  1. Pingback: E-mail the selected record in #powerbi with #powerapps and #flow and include a CSV File with the records — Erik Svensen – Blog about Power BI, Power Apps, Power Query | MS Excel | Power Pivot | DAX | SSIS |SQL

  2. Pingback: Emailing Data From Power BI Via PowerApps and Flow – Curated SQL

  3. Pingback: Power BI Paginated, DAX, Power Query and more... (April 22, 2019) - Learn Power BI

  4. we have try this scenario but not successfully and could you implementation this scenario send it and details.

  5. Hi Erik,
    I`m trying to implement the same thing which you have put here and while I tried to refresh/reopen the developed app it remove all the records or data but, the schema stays there.
    Even, I tried to download the .pbix file after publishing the app to the services and open the downloaded report in power bi desktop and publish it to the services with other name it gives me the same issue which is schema is present but, records are not there.

    Please help me out.
    You can reach me at — akshar.khendelwal.ext@nokia.com

  6. Hi..
    i used the term “schema” for the table and labels that have been defined in the htmltext box, text box for email,email icon.
    but, when I restart the power app and power bi services there is no data in this schema.
    I can share the screenshot with you but I can`t find the attachment button.
    Please share your email id if possible.

  7. Hi Erik,

    Very useful!

    I’m struggling to get the JSON code into the Schema in the Parse JSON step. Should this be autogenerated, or did you write it yourself. If the later, can i kindly ask you to share the full code, that you used?

    1. Hi Jonas,

      The easiest way to get the JSON code is to run your flow (and it will fail) – but then open the runs from the flow property page
      and take the value from the inititalize variable step and use that in parse JSON setup.

      If you provide me with your e-mail I can send you a copy of the flow

      BR
      Erik

      1. Thank you!

        If I have an image in one of the columns of my html generated PowerApps table, would that image also show as an image in the .csv file then? Or would it just be the url of the image?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s