Tutorial: Power Apps in Power BI
It just makes your life so much easier
The focus topic at BI & Breakfast in November is Power Apps in Power BI. Leon Potthoff introduces the 50 or so participants to the world of Power Apps as part of the Microsoft Power Platform in his usual routine manner. With an illustrative example in tow, it quickly becomes clear that Power Apps can not only automatically optimize reports, but also makes work incredibly easy thanks to the writeback function alone.
Once a month, Leon Potthoff guides his loyal Power BI fans through the world of Power BI. And the community is growing steadily. The possibilities offered by Microsoft's analysis tool are diverse and varied thanks to regular updates, but not always intuitive. That's where Leon comes in. With monthly focus topics, presentations of the most important updates and an open discussion round, he helps busy analysts to optimize their work with data in Power BI.
Power Apps on the Power Platform
Power BI and Power Apps are tools of the so-called Microsoft Power Platform applications. Alongside Power Automate, Power Virtual Agents and Power Pages as well as Data Connectors, AI Builder and Dataverse, they are important tools within a targeted data stack. Power Apps is an app development platform and can be classified in the Embedded Applications category, meaning it is embedded in a Power BI report page as an external program in the canvas and the filter context. The service is integrated into Microsoft 365.
With Power Apps, companies develop low-code solutions for business challenges. Users can create custom applications without having to use traditional development methods. It offers a drag-and-drop based interface so that applications for web, mobile and other platforms are created quickly. Or, as Leon would say: "It just makes your life so much easier."
Canvas apps and model-driven apps
Within Power Apps, a further distinction is made between canvas apps and model-driven apps. With canvas apps, the focus is on the user-defined user interface (UI). Users start with an empty canvas or an empty template and open a connection to any data source. There is full flexibility in app design, both on the web and on mobile devices. Canvas Apps is free when using the standard connectors, provided that at least a Microsoft E3 license is available.
For model-driven apps in Power Apps, the focus is on the data structure and business logic derived from the underlying Dataverse. The data source must be based on Dataverse. In contrast to canvas apps, users have limited flexibility in app design. Model-driven apps can only be used on the web. They are subject to a fee and tied to Dataverse.
Connecting Power Apps with Power BI
Leon Potthoff then puts all the theory into practice. For anyone who missed the CoP event, we have summarized the most helpful facts here. So let's jump with Leon into the Power BI service and an example Power BI report. The following section will focus on employee management, which will be much easier to organize with the Writeback function via the Power App.
The table created in Power BI sometimes contains incomplete and unmaintained data. Long-suffering analysts will be familiar with this: To update the data, it is usually necessary to jump out of Power BI to update the original table (e.g. Excel). This table must then be loaded into Power BI in order to access the updated database. Power Apps puts an end to this tedious process.
In his example, Leon can use the Power Apps field (shown in the screenshot at the top right of the screen) to change the data record directly in Power BI. To do this, he only has to click on the corresponding row in the table on the left so that the complete data record appears in the Power Apps field on the right. Leon can change and save the data manually in this field. The data is then not only changed in the Power BI report, but is also updated and saved in the original database, in this case a PostgreSQL database. Depending on the process, data could even be maintained exclusively via Power Apps.
Data records are changed directly in Power BI using Power Apps and are then returned to the database.
Another example is holistic planning. With Power Apps, it is possible to incorporate planning data directly into a report. The report then no longer only shows the current inventory and the forecasts based on it, but also allows a look into the future, taking individual parameters into account. Filters can be set individually.
Architecture of Power BI and Power App
How are Power BI, Power Apps and the database connected? In Leon's example, the data is stored in a Postgres database and is connected via Direct Query. Direct Query establishes a live connection between Power BI and the database. If something changes in the database, one click on the visualization is enough and the data is updated directly in the visualization.
Once the data has been connected, the next step is to create a Power App in the Power App Center. The Power App is then connected to the Postgres database. The Power App is now made available in Power BI, which can immediately interact with visualized data and, conversely, writes changed data directly back into the database.
Create Power App in Power BI
How is a Power App created in Power BI? Once the data has been connected to Power BI, the relevant data records can be selected accordingly in the table. At this point, you can already start designing a Power App. In the "Visualizations" area, the "Power Apps" visualization is available by default. A window opens with a short instruction guide.
Here we now select the fields that are to be used for the visualization. We then add an existing app to the report or create a new app. Once published, it can be used in the report and is continuously updated.
If we create a new app, we are redirected to the Power App Center via the URL make.powerapps.com. The Power Apps interface then opens. The navigation is based on familiar Microsoft products. Various properties can be set for selected elements via a drop-down menu at the top left. The "fx" line at the top of the screen enables the use of the low-code language Power Fx, which can be used to customize individual functions. On the left-hand side is the structure view, which can be used to add a wide variety of elements such as input, display, layout or shapes. A wide variety of data can also be connected via the "Data" field on the left. To change the view or layout of the app, we use the "Properties" columns on the right-hand side.
Once all the parameters have been set as required, we save the app so that it can be published in Power BI. This works via the icon at the top right of the screen, which shows a small arrow pointing upwards. We give our app a name and save it.
If we now switch back to Power BI, we see that the elements selected in Power BI are displayed directly in the app.
Done! The desired data records can be edited in both Power BI and Power Apps. All changes are recorded and saved at all levels.
The Power Apps Visual is present in Power BI by default and reacts to interactions in the Power BI report. If it is not visible, this can usually be solved via the administrative settings. Power Apps can be used free of charge with E3 licenses. There are certain restrictions, for example only 1,000 rows can be loaded in the Power BI report with the Power App. The benefits of Power Apps should therefore always be checked in advance for each use case.
BI & Breakfast in December
In the next Community of Practice, Leon talks about the versioning options for Power BI reports and covers the topic of Calculation Groups. Custom map visuals will also be discussed. If you are now even more interested in Power BI, you should join us on December 6 at 10 a.m. for the next BI & Breakfast with Leon Potthoff. See you in four weeks!
Register now for the BI & Breakfast Power BI!
