Embed PowerApps into Power BI Dashboards – Part 1

Having read Matt How’s blog (found here) about PowerApps, not only did it get me interested in the technology, I also wondered how well (if at all possible) it could integrate with Power BI. Our friend Google soon told me that it was already possible to embed PowerApps into Power BI, released in the April update. However, apart from this blog by Ankit Saraf, there aren’t many professionals sharing their experiences.

In addition, leveraging Direct Query mode in Power BI means we can simulate real time user input and reporting. To replicate my solution below, you will need an understanding of PowerApps, Azure SQL Database, Flow and the Common Data Service (CDS). The Further Reading section provides some good links to get you up to speed.

I have broken the blog into 2 parts:

–          Part 1: How Power BI visuals and PowerApps can be used together.

–          Part 2: Benefits and Drawbacks of the tools/processes used.

Solution

I picked a typical use case that would link Power BI and PowerApps – Actual vs. Target. The Power App will be used for adjusting target values, whilst an Azure SQL Database will contain the original target and actual values. All data and Power App interaction will be embedded into a Power BI Dashboard.

Create Sample Tables and Data in Azure SQL Database

Create and populate two tables – dbo.SvT for static actual vs. target data and dbo.SvTAdjusted that will eventually contain the adjusted target data from the PowerApps form.

Create PowerApp

Create an Environment within the PowerApps service, adding two new Connections:

1.       Connection to the CDS, using my company Microsoft account. This is where the adjusted budget values reside.

2.       Connection to the Azure SQL database, which will become the destination table to store the CDS Power App data.

The next step is to import the SQL Data from dbo.SvTAdjusted directly into a CDS PowerApp.

This automatically creates a user form containing the data. Here is where you can customise the PowerApp, such as making fields read only and configuring look and feel.

Publish the App and test and change the ‘Target’ values to test.

Create Flow trigger

Navigate to https://emea.flow.microsoft.com/en-us/ and login. Create a new flow, searching for ‘Common Data Service’ as the connector. Select the below and create the Flow.

Select the PowerApp CDS Entity (Adjusted Target) as source.

Add a new step (Add an Action) and search for ‘SQL Server’. Select SQL Server – Update Row as the destination and map to the dbo.SvTAdjusted table. The column data types between CDS and Azure SQL Database must match when being mapped. Save the Flow.

Create Power BI Report

Create a Power BI Desktop report and connect to the Azure SQL Database.

Set up the one to one relationship on ‘PrincipalID’, between the tables.

Create some KPI’s and a table to compare dbo.SvT and dbo.SvTAdjusted metrics. In the below example, the ‘Adjusted Budget’ metric will change when we make changes in the CDS Power App.

Embed Power App into Dashboard

Publish Power BI Desktop report and pin as a live page. To embed the PowerApp into the Dashboard, add a Tile and select Web Content. The App ID can be found under Apps in the Power Apps web portal. Simply paste the App ID into [AppID].

The PowerApp is added as a Dashboard tile. It is now possible to change the ‘Budget’ values.

Time to test everything works. Change the values for all three records and refresh the Power BI Dashboard. The values have changed almost instantly!

Further Reading

Check out Part 2 of the blog, where I will be discussing the benefits and drawbacks I have found with using Power BI and PowerApps together. Find other recommended resources below.

o   Matt How’s Blog – http://bit.ly/2CpbTYI

o   Embed PowerApps into Power BI – http://bit.ly/2ywgsNX

o   PowerApps – http://bit.ly/2Brjys4

o   Flow – http://bit.ly/2CoL2vW

o   Common Data Service – http://bit.ly/2CnXXhv

Contact Me

If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.

Twitter:                @DataVizWhizz