Integrating Power BI and Power Apps can give great insights as well as the ability to react right away. In this eBook companion, we will look at how to expose a Power App inside a Power BI report as well as having a Power BI report being made available from a Power App.
What is the Power Platform?
The Power Platform is Microsoft’s family of no-code/low-code tools. Power BI, Power Apps and Power Automate (formerly known as Flow). They sit on top of the Common Data Service, which stores all of your structured business data and makes it secure and usable across all of your applications.
On top of the Common Data Service are hundreds of connectors that allow you to connect to all your existing services without having to write any code at all. In addition, you can connect to your on-premises services by using the On-Premises Data Gateway. This will allow you to connect to services such as SQL Server, or any services which are surfaced through an API.
All of this is possible without having to move the data to a cloud service, but still being able to provide transformation of the data using the Power Platform.
The big change for Microsoft
In terms of Microsoft’s presence in the low-code/no-code space, a year ago Microsoft was not considered to be a contender. Now, with the large-scale adoption of the Power Platform, they are one of the leaders amongst some of the more high-profile companies in this space.
The reason that they are here is because they are beginning to figure out how to integrate all of these applications together. Rather than having an individual Business Intelligence (BI) application, or a standalone SharePoint Application, they are starting to merge elements together to provide a unified approach for users, being able to get to whatever application and information they want from anything within the platform.
An Integrated Low-Code Platform
Microsoft’s vision for the Power Platform stemmed from a recognition that data is flowing in and around everything. It is a fact that people need to communicate with data, and data needs to communicate with other data. It is also possible that through AI and Cognitive Services, that data may be coming back to initiate conversations with the user.
The premise is that organizations that can harness their data using no-code or low-code platforms are going to be able to drive those intelligent processes much faster, reducing time to market, and as a result, will be able to out-perform those that don’t.
Power BI, Power Apps, and Power Automate are the trio of products that are going to enable organizations to do that.
Power Apps is the broker for the initial conversation. This needs to act on data, drive the process, or augment data in order to drive the business process ecosystem.
Power BI is the analysis engine. If we can get Power BI to get more data, and more intelligent data, then we can come back to Power Apps and Power Automate with more intelligent decisions, or more actionable processes which can be used to leverage the next steps in the business organization.
Power Automate gives us the ability to perform intelligent workflows in order to bring the business process full circle and feedback into the conversation.
Pulling Everything Together
In order to demonstrate each element of the conversation, we are going to take an Excel spreadsheet and connect it to a Power BI report. We will create several visualizations, use slicers and filters to gain understanding and insight into the data and get a better feel for the state of our company.
We will then take a Power Apps visualization and connect it to an approval workflow.
Our user is Jim, a Service Manage in Austin, Texas. He currently spends hours reviewing reports and understanding how many discounts are given on products and services within his division.
What Jim needs to do is manage approvals more effectively and more efficiently. He wants to get his job done faster, as he has lots of other priorities that need to be addressed. Jim uses a mobile and a tablet and so wants easy access to the data which is going to allow him to make decisions quickly and effectively.
In order to find a solution to Jim’s problems, we will look at Office 365 and see how Power BI and Power Apps can help him.
Storing our Data
The first thing that we are going to do within our scenario is upload an Excel spreadsheet. The spreadsheet that we are going to use can be found at:
Once you have downloaded the spreadsheet, you can then upload it into Cloud Storage, such as to OneDrive for Business. This could also be uploaded to SharePoint but for illustration, we will be using OneDrive.
In order to make the spreadsheet work within this demo, two additional columns have been added to the downloaded spreadsheet.
- Approval Comments
- Discount Percentage
A key piece of advice is that it is much easier to keep track of where you are working with data if there is a unique identifier, or a key, to your data, as shown by the ID column. Ideally, it will be a number, so we can format the column to be a number column and remove any decimals to show just an integer.
Power BI Report
On our Power BI report, we have a number of visuals, including the profit by date, the sales representation, a map of the world displaying sales data, a discount percentage selector which is a slicer, and a view of the data in the spreadsheet including the ID column which we created.
In our scenario, Jim needs to approve any discounts which fall into the High discount band; therefore, we can use a Power App to manage the approval. Therefore, if we select the record with the ID: 5 in the table, then we can feed this into the Power App in order to manage the approval process.
If we used the Power App to approve the row then it will write back to the Excel spreadsheet which we stored within our OneDrive for Business.
Within the top left corner of our Power App, we have a small graph icon, which, when clicked, will change the screen within the app and bring us back to our Power BI visualization, showing more sales data.
The data within this tile is dynamic; therefore, if I were to select another row, then this data would change based on the user selection.
Note: One key thing to remember though, is that if you or anybody else have the Excel spreadsheet open for editing, you will be faced with an error which states that the file has been locked.
If you ever see this error, you will need to close your spreadsheet and refresh your Power BI dashboard before trying to run the update again.
Now that we have seen the finished solution let’s step back and look at how to build it.
Building the Solution
This eBook assumes a familiarity with the Power BI web user interface as well as a basic understanding of the Power Apps user interface. It will not go into depth into how to use the tools, it will instead highlight where the key elements are.
Power BI: Connect a Data Set
The first thing that we are going to do is upload and connect a dataset so that we can begin to build a report from it. From the “+ Create” button at the top of the screen, I can select “Dataset”.
When we start to create a dataset, we have a number of different data sources that we can select data from such as services, databases etc. In this case, we are going to create the dataset from Files, as we are taking the data from an Excel spreadsheet.
When we choose files, we again get several options presented to help us locate the file that we want. We have the ability to upload a file directly into Power BI, we can select OneDrive for Business or Personal, or SharePoint. So, wherever our file is stored, we can pull it in and begin using it. In this case, we are going to select OneDrive – Business.
By selecting OneDrive – Business, Office 365 will automatically authenticate you against OneDrive, and then present your files. We can then select the spreadsheet that we want.
There are two ways in which I can retrieve my data from Excel using the Import or Connect options
- Import Excel data into Power BI – data is refreshed automatically from OneDrive.
- Connect, manage, and view Excel in Power BI – this allows me to keep my workbooks up to date with a scheduled refresh
As I want my data to be easily available to both Excel and Power BI, we will select Import. This could take a couple of minutes, but it will create the dataset and then create a dashboard under the relevant tab.
Once we click on open the dashboard, it will allow us to start to build out our insights.
Building a dashboard
When we open the dashboard, all of the data from our Excel spreadsheet is available to use to start providing immediate insights into the data. Within the editor, we have Fields, Visualisations and Filters.
These areas start to build up the dashboard area, so for example, if we put a simple chart on the dashboard, with an axis of date, and the fields set to profit, then the dashboard will update to show profit by date in a visual format.
What we really want to do with this data, however, is to create a report.
Creating a Report
If we return to the workspace and click on the Datasets tab, we will see all of our Datasets. Associated with each dataset we have a number of actions available, one of which is Create Report.
Alternatively, the reports could also be created from the Reports tab, or by clicking + Create in the top right corner.
Creating the report will again load the data from the Excel spreadsheet and will load a blank canvas for us to start placing our visualizations. In the same way, as we did on the dashboard, we can place a number of visualizations onto the screen and configure them to display the data that we want. We will therefore add the “Profit by Date” visualization to the top right of the screen, and the “Sales by Product and Segment” visualization in the top right.
The “Profit by Date” visualization is a standard bar chart, configured as it was when we spoke about building a dashboard. The “Sales by Product and Segment” is a Stacked Line Chart configured with Product, Segment and Sales so that you get the stacked effect.
Next, we want to show sales by Country that the sales were being made from, which when we drag country onto the canvas, it will automatically render it as a map. It will automatically put the points on the map control relating to the various countries, so I don’t need to do any more configuration myself.
We will now create a table that will contain the information which will make it possible to interact with our Power App. We can simply drag the table visualizer from the Visualisations took kit onto the canvas so that it presents a blank table. From there, we can simply drag the fields that we want, into the Values property of the table.
We will start with the ID column so that we can ensure that we are selecting the right row and that we’re interacting with the correct data when we get into the Power App. When in production we could consider removing this field, but while we’re in development, we should present it.
In addition to the ID field, we will add the Month, Gross Sales, and the Discounts Column. From there, we can use the calculated field “Discount Band” as Jim is only interested in the discounts that fall into the High band and can also add the Product.
In order to make Jim’s life easier, when interacting with the data, we can add a Slicer to filter the data within the table, so that it will only show discounts that fall into a Low, Medium or High band depending on which option the user selects.
To add a Slicer, we simply select the Slicer from the toolkit, and select Discount Band as the field. A couple of additional configuration items to change, to give it the desired effect, is to click on the formatting options for the slicer. Formatting is denoted by the paint roller icon and will open up a number of different options for you to change on your visualization. In this case, we are going to go to the general group, and change the orientation from Vertical to Horizontal which will create a more button-like experience rather than vertical checkboxes which feel more like Excel filters.
When we click High, everything on the Chart filters, e.g. the Profit by Date changes. This isn’t necessarily the experience which we want, we only want the areas of the chart such as the table to react to this slicer being used. In order to change this, we will need to select the slicer and then click on “Visual interactions” which is located at the top of the canvas.
From there we can switch on “Edit Interactions” which will then allow us to define whether a visualization is updated on a case by case basis. Each visualization will then have a stop sign presented in the top right corner to indicate if it is interactable or not. By default, it will interact and will show a “hollow” stop sign.
Once it has been clicked, it will become solid, and therefore means that it will not change based on the slicer being updated.
Once we have stopped interactions with the top visualizations, then we can happily select Low, Medium or High from the slicer, and watch as our data table filters for us, but the overall graphs remain the same.
Once we have placed our main data visualizations onto the canvas and then configured them, we can bring in our Power App.
Adding a Power App
Within the visualization’s toolbox, you will find a Power Apps icon. When we click on the Power App icon, it will place the Power App visualization onto the canvas.
If you don’t see this, then please check to make sure that you have access to Power Apps, and if not, speak to one of your administrators. Alternatively, if you’re using the Power BI Desktop, ensure that you are using an up to date version.
The default content for the Power App visualization is a user guide walking you through exactly what you need to do to use it. The slight nuance here is that you cannot select your app straight away. Instead you need to provide the visualization with a field first of all, and then the view will change to allow you to select an app.
We can place any of our fields listed into the Power App data property, so if we need to pass multiple pieces of data through, then we are able to do that.
One thing to note at this point is that the ID field has been placed into the property as “Count of ID” which isn’t what we want. We actually need it to pass in the value stored within the ID field, therefore if I click on the ˅ icon, then I can change the behaviour from Count to Not Summarize which will mean that it will just pass the individual field value through.
Once we’ve defined the Power BI behaviours, we can go back to the Power App visualization and either choose an app, if we’ve already created it, or create a new one from scratch.
Creating a New App
For the purpose of this demo, we will create a new app, which will automatically open up PowerApps and take us through the authentication. You will generally be authenticated twice, once for Power Apps and once for the Connector.
The app will autogenerate a number of controls, including a screen with a gallery control on it. The gallery control is a control that will repeat an area for every row contained in the data source. The thing to notice is that, by default, the app will be created in portrait mode, meaning that the app is taller than it is wide.
What we actually want, for this solution, is for the app to be in landscape mode so that it will be more effective in the space available in my Power BI report. To change this, I can go to File, and then to Settings, and change the orientation to landscape. The generated app will scale to the screen dimensions automatically.
When I select Gallery1 from the Tree view, the Items property is set to Select(Parent). If I click on the PowerBIIntegration, found just above Screen1 in the Tree view, and then click back to Gallery1, the Items property will change. The new value will reference PowerBIIntegration and will be using the Data property.
With the Items property set in this way, it will return all rows. However, I’m only ever going to be interested in one row; therefore I can encapsulate the Data reference within the First() function. When I change the property to First(‘PowerBIIntegration’.Data) then it will only return the first item provided by Power BI.
Within the rows of the gallery, I can add multiple controls and can reference the data from Power BI by providing the control with ThisItem.NameOfColumn e.g. ThisItem.Id. This will then show the data from that column in the gallery row.
In the final version of the app, we have the gallery, which was automatically created, but we also have a Form which acts as the interface directly with the Excel spreadsheet. That is the area on the right-hand side showing the “Approved” and “Approval Comments fields.
For us to be able to do this, we need to create a Connection to the Excel Spreadsheet by using the OneDrive for Business connector. When you use this connector, it will present a list of spreadsheets that you can potentially connect to within your OneDrive. In this case, our data source is called financials.
When we configure our form, we need to tell the Item property which rows within the spreadsheet it is going to interact with. For us to do this, we need to filter the rows based on the data that has been provided to the gallery so I will use the following formula:
This formula will use the ID column within the spreadsheet and will look for a row that contains a match to the ID being provided from the gallery.
The filter formula will return a table of data, but again we are only going to be interested in the first row. So, by updating the formula with the First() we can isolate just the single row:
All of the work for updating the Excel spreadsheet takes place on the save button at the bottom of the screen, with the logic being written into the OnSelect property.
There are a number of moving parts here so we will look at some of the key items.
- Set(validateOk,false) – This is setting the current validation status to be unvalidated, meaning that we want to do some validation before we submit the form.
- Set(currentId,Value(DataCardValue5.Text)) – This is a hidden field in my form, which is making sure that I have a string value which I can use to perform checks
- If (IsBlank(DataCardValue9.Text) – this will check to see if the approval comments box is populated or not. If it is not populated, then it will show an error message and won’t save back to Excel
The latter part of the formula simply submits the record back to Excel, with another update being performed to an individual field using the Patch command.
Power BI within the PowerApp
The final part of the Power App is to add a Power BI visualization within the app itself. To do this, we will create an additional screen within the app called “PowerBI Screen” and navigate to it by placing an icon on Screen1. For this demo, we will use the Trending icon, and then set the OnSelect property to navigate to “PowerBI Screen” by using the formula:
The PowerBI Screen will follow the same pattern as what we had with Screen1, where we have a gallery displaying some of our key information. The key item in this gallery is the Product Category, as we will be using this field to drive the Power BI tile on the right side of the screen.
But before we can start using PowerBI here, we need to do a little more work in PowerBI itself. In the report window, we need to add a new tab at the bottom, in this case, it is simply called Page 2 on which I have placed a line chart visualization.
That line chart simply shows the dates on the axis, products within the legend, and gross sales as the values. Once we have done that, we need to export the visualization or pin it to our dashboard so that we can use it again later. To achieve this, I need to click on the pin icon in the top right corner of the graph and then select the relevant dashboard once the dialogue window appears.
In this case, we have selected the Financial Sample dashboard. Now that we have configured our tile, we need to return to our Power App to complete our configuration there.
Within the Power App, we need to add a PowerBI tile to the screen, this can be found under the Insert menu, in the Charts control group. When it is added, it will open a selector for you to be able to select the exact tile that you want to be added to the screen, by filtering down through the workspace, to the dashboard, and then to the tile.
This will then present the default view of the visualization to the user; however in our scenario, we want to be able to filter this to a specific product category. Therefore, we need to modify the TileUrl which is available from the properties blade on the right-hand screen, under the Advanced tab.
The embed URL has automatically generated for us, so the only thing we need to add is the query string parameters to filter the data. We can, therefore, enter this straight into the TileUrl box, adding a filter OData query to the query string. If we wanted to filter the products down to the selected category, we would append the following:
&filter=financials/Product eq ‘” & Gallery1.Selected.Product & “’”
Breaking this down:
- &filter is the query string parameter that is going to tell the report to filter based on a defined criterion
- financials/Product is the data source and column that we are going to filter on
- eq is the OData notation for equal to (gt = greater than, le = less than or equal to, etc.)
- Gallery1.Selected.Product refers to the active data row in Gallery 1 and references the Product field
If we want to test this in Power BI, we can do so by going back to our visualization and working within the URL box. In this scenario, we are filtering based on the Product being “VTT.” The main difference to note here is that because there are no other query string parameters, there is no preceding “&” it just simply has “?” which tells the application that the following parts of the URL are parameters. Also, be aware that the parameter names are case sensitive, so be aware of the names you’re referencing in the URL and make sure that they match your column names in Excel.
Once I have configured the look and feel of my app, I can save the app and then return to Power BI. When I open up my Power BI report window, the app will automatically load in the visualization which I’ve already put on the screen. So now, when I click on a row in the table, our Power App will automatically update.
We have now had a look at how much more closely the three products within the Power Platform: Power BI, Power Apps, and Power Automate, are becoming. With some of the latest updates, you can now add Power Apps into Power BI and also surface Power BI visuals within a Power App.
When we add Power Apps to our Power BI report, we need to ensure that we provide the app with the data fields that we want it to consume by dragging them from the fields blade into the Power Apps data property.
When we have added the Power Apps tile, we have the ability to create an app from scratch or add an existing app if we have already created one. If we create one from scratch, it will create an app in portrait mode, but you have the ability to change the orientation directly within Power Apps.
The app itself will be generated with a single screen and a gallery that is tied to the data provided from Power BI through the PowerBIIntegration located above Screen1 in the Tree view. The gallery, when first accessed, will not show the true source of data until you click on PowerBIIntegration and then click back to the gallery. The items property will then be updated to reference the data being passed in from Power BI.
From this point, you can start to build out your app until it has the functionality, look and feel, and data actions that you want. You can save the app at any point, and the Power Apps visualization in Power BI will automatically be updated to display the app that you have created.
You also have the ability to place a pinned Power BI tile into your Power App by selected Insert, Charts and then selected Power BI tile. This will automatically populate the property TileUrl, however if you want to add filters in then you can modify the query string parameters by including the Filter parameter, and then building up an OData query.