The PASS Community has given me so much over the last twenty years (yes, it really has been 20 years!), that when I was asked if I could contribute an article for the PASS Insights: Developer newsletter, I didn’t hesitate to say, ‘Yes, I’d be honored to.’ 

I’ve been thinking about a problem I had and was trying to find a way to solve it. Being a Microsoft MVP, one of the things you have to do is keep track of all the community things that you do. This started out as a simple spreadsheet.

It wasn’t long before I knew I needed more, so I created a few Power BI reports to sit on top of it. This gave me the ability to see answers at a glance for questions that kept coming up. What I soon discovered was that I also needed a way to update this data as well as view it.  As a data professional and a former application developer, I decided that there had to be an easier way to do this than to update a spreadsheet, refresh my Power BI desktop file, then publish it every time I needed to make a change.

This article is about how I embedded a PowerApps custom visual in my Power BI report to keep up to date with my latest community activities.

I decided to start small by addressing just the speaking engagements that I have to keep track of. I loaded my spreadsheet data into an Azure SQL Database and pointed Power BI at the database. Next, I needed to figure out how to add a PowerApps custom visual to my Power BI report. There are lots of good resources out there to get you started. Charles Sterling of Microsoft has a really quick video that shows you just how quickly you can get up and running, and Devin Knight has one on how to create PowerApps. Between these two videos, which were less than 25 minutes combined, I was able to add a PowerApps customer visual to my Power BI report and create a very simple PowerApp to update my data.

Pre-Requisites

First, you’ll need a Power BI account and a PowerApps account. Depending on your Office 365 subscription level, you already have both of these. If not, you will need to sign up for either (or both!). Costs will vary depending on your Office 365 subscription—if you are going that route—or if you’ll need to purchase a paid subscription for Power BI and/or PowerApps.

Next, you will need to make sure you have either Edge or Chrome installed on the machine you are using. These are the only two browsers PowerApps currently supports for development and they are also the only two that Power BI supports for the customization of the PowerApps custom visual.

There are two steps, adding the custom visual to the Power BI report and creating the PowerApp.

Adding PowerApp Custom Visual to Power BI Report

I just wanted a list of my speaking engagements that were stored in my Azure SQL Database, so I created a table visual that looked like this:

Next, I needed to add the PowerApps custom visual to my report. The PowerApps custom visual is not one of the standard visualizations that comes with the Power BI service, so I had to go to the Marketplace to download it before I could add it. To download a custom visual, you need to click on the ellipses in the Visualizations pane and select Import from marketplace.

Then type PowerApp in the search box and click the search button. Once it displays the PowerApps visual click the Add button.

 

The visual is now available for adding to your Power BI report. Click the PowerApps icon to add it to your Power BI report canvas. Once there, resize it to your desired size. One of the cool things about this visual is that it gives you instructions on how to use it (if you don’t see them, just resize the visual to see all the instructions).

Following the directions from the visualization, drag the fields from your dataset that you want to use in your app. Once you’ve done that, the visual changes and you will be asked if you want to create a new app or an existing app. Since I hadn’t created my app, I selected Create new.

After you click Create new, it will open a new browser window at PowerApps.com.

Creating the PowerApp

It starts by asking if you want to create a form, gallery, or if you want to take an interactive tour. I selected the interactive tour when I first started to give me a better understanding of what I was about to do. I suggest you skip all of these options and go directly to the newly created app. This newly created app has already added a basic gallery that uses the PowerBI Integration object as it’s data source. This is a really important thing to take notice of. This is what links our app back to Power BI. We will use the gallery that was automatically created to act as our filter for the data source we are going to add next, in order to send updates back to our database.

Let’s clean this auto created gallery up a little bit and give it a meaningful name. I chose PBIGallery because it’s a gallery with a connection back to Power BI. I also know from playing around that I want to change the orientation of my app to landscape. To do so, click File from the menu then select App Settings from the left navigation pane, Screen size + orientation under App settings, and finally select the Landscape under Orientation. Don’t forget to click Apply at the bottom right of the screen.

Now we need to add a data source so we can send updates back to the Azure SQL database. From the menu select View, Data Sources then click + Add data source.

The Data slider changes to list any data sources that you’ve previously connected to, but there is also a + New connection that you can click to add a new datasource. Since I’m working with Azure SQL Database, I did have to provide the servername and the appropriate credentials of a user that has read and write capabilities for the tables I will be updating. After selecting the SQL Server connection it provides a list of all the tables that are available in the connection. We are working with the Events table, so I select it and click Connect.

Now that I have a data source, I can add a form to my app and connect it to the data source I just added. From the PowerApps menu select Insert, Forms, and select Edit, because we want to be able to edit the data in the form.

With your newly added form in focus, from the Properties window on the right hand side, select the Data source drop down and choose the dbo.Events data source.

Now add the same fields that we added to the Power BI PowerApps custom visual

We don’t need all the fields to be editable, just the EventAttendees column, so I changed the control type to View text in the drop down and arrange the columns on the form in the order I wanted them displayed.