Guide: How to add Write-Back capabilities to Power BI reports with Power Apps — Part 2

Guide: How to add Write-Back capabilities to Power BI reports with Power Apps — Part 2

Introduction

This article is part of a series on integrations between Power BI and Power Apps.

Part 1 showed how to get started with creating and embedding a basic Power App in a Power BI report for data write-back.

Part 2, this article, will teach you:

1. How to pick a data source for your Power Apps output data

2. How to output data to the selected source

3. How to make the data appear Live in your Power BI report, without the user having to refresh anything.

1. Choosing the right Data Source for your Power Apps output

Before we can show any kind of data created from Power Apps in a Power BI report, we need to store it somewhere. Choosing the right Data Source for hosting your data is an important question. Most often, the choice is between Sharepoint, a SQL server and Dataverse.

Two main tradeoffs may influence your choice:

1. Timeliness of data (update frequency) vs. License Costs

2. Security & Governance vs. Availability

First, you need to ask yourself (or your users), how frequently data needs to be updated. If the answer is less than 8 times a day, you may be just fine using Sharepoint as a Data Source. If you need more than that, perhaps even live data, you will need either a SQL Server or Dataverse Connection. The kicker however, is that a Power Apps using Dataverse or SQL Server Data Connections, will require Power Apps Premium Licensing, as opposed to the standard license required for Sharepoint. Refer to Microsofts own website for guidance on licenses: Link to Power Apps Licensing

Second, you may want to consider how sensitive your data is, and how accessible it should be. Sharepoint is easy to get started with and accessible for users, but also has limited governance and security options. Dataverse and SQL Server allows much more comprehensive security setups. If you are faced with picking between the latter options, it will often be a matter of preference, and it might simply be the easiest to piggyback off of any existing solution you may have in your organization.

In short:

Need low cost, low security, infrequent data refresh or high accesibility? Pick Sharepoint.

Need high security or frequent/live data refreshes? Pick Dataverse or SQL Server.

2. Outputting data to your data source

After selecting your data source, prepare an empty table at the destination with the correct columns and data types you wish to input. I am going to input Sales Budgets by Product Category, and have created a table in an SQL database with three columns. Make sure that you define a Primary Key on the table if you are using a SQL Server, as that is required to update records in the table from Power Apps:

Now, open up your Power BI report with your embedded Power App. The best way to work with embedded Power Apps, is to open the App from inside Power BI, as that will send data from Power BI over to Power Apps for you to work with. To do so, open your report from app.powerbi.com, enter the Edit Mode for the report, open the menu for the Power Apps visualization, and select “Edit” here. This should prompt your Power App to open in edit mode in another window in your browser, with data from your Power BI report available.

With the Power Apps studio open, find the Data tab on the left hand side, add a new data connection to your data source of choice, and connect to the table you created.

Now, you need to create a screen in your App for submitting data to your new data connection. I have created a simple input interface with drop downs and text input controls allowing users to input budget amounts on Product Category, as well as a button control which submits the data to the data source:

Note that I am not using a Form control here, but instead built my own form with simple controls as seen in the Tree View. While Forms may seem simpler to setup initially, they are difficult to customize, and are very bad at submitting multiple rows of data, if you ever run into that requirement. Instead, I configured my Submit Budget button with the following code:

With
    {
        BudgetExists: LookUp(
            SalesBudget,
            ProductCategory = dd_Category.Selected.Category
        )
    },
    If(
        IsBlank(BudgetExists),
        Patch(
            SalesBudget,
            Defaults(SalesBudget),
            {
                InputID: Last(SalesBudget).InputID + 1,
                ProductCategory: dd_Category.Selected.Category,
                SalesAmount: Value(ti_BudgetValue.Text)
            }
        ),
        Patch(
            SalesBudget,
            BudgetExists,
            {
                SalesAmount: Value(ti_BudgetValue.Text)
            }
        )
    )
);

The code uses two of my favourite functions: With(), to lookup if an input already exists for the selected ProductCategory, and use the result repeatedly throughout the code for efficiency, as well as Patch(), to write new records and update existing records in the data source, instead of using a Form.

Further, i decided to populate the Text Input control with the default value of the selected ProductCategory, if a value already exists:

LookUp(SalesBudget, ProductCategory = dd_Category.Selected.Category).SalesAmount

With your custom form setup, save and publish your Power App, reload your Power BI Report, and Test that you are able to create rows of data inside your data source, using the embedded Power App.

3. Retrieving the data with Power BI, and displaying it live for users, without the need for refreshes

Now wouldn’t it be nice if those values we input with our App just show up in the same report? I would say so!

In Power BI, add a new Data Source to your report, and connect to the Data Source and Data Table in which you outputted your Power Apps data. During this setup, you have an important choice to make: To DirectQuery or not to Direct Query.

If you went with the low cost/low security route of Sharepoint, you are unfortunately limited to Import mode, which will make your data update only when your Power BI dataset is being refreshed.

If you are using SQL Server or Dataverse however, choosing DirectQuery will enable you to query data from the data source live, and new/updated data coming from the Power App may be reflected immediately in your report. However, DirectQuery comes with a number of limitations you should be aware of. See the Microsoft documentation for more details: Link to documentation.

I will select DirectQuery, as I wish to provide my users with live feedback on the impact of their inputs. After setting up the new Data Source, create any relevant relationships to other tables in your Data Model. You can now build a visualization using the new data coming from the embedded Power App, or you may use the data in existing visualisations as well! Here I have chosen to show the Budget Amounts inputted in my Power App, together with the Sales Amounts from my original dataset.

If you save and test your report and app now, you will most likely disappointedly notice that when you Submit Data in your Power App, nothing happens in Power BI until you refresh your browser. What is actually happening, is that DirectQuery is not truly “live”, but only occurs every time a visual refresh takes place. Luckily, we can force this to happen automatically!

Return to Power Apps Studio, and find the button control that submits your data to the data source. You simply need to put the funciton called PowerBIIntegration.Refresh() at the end of the code of your button’s OnSelect property, to trigger a visual refresh of the entire Power BI report after you are done patching data to the data source. This effectively makes the data show up immediately in the Power BI report.

There is one catch to the PowerBIIntegration.Refresh function: It is only available within Power Apps which have originally been created from within the Power Apps for Power BI visualisation type in Power BI, by clicking on “Create New” inside the visualisation. The function is not available in a Power App created ‘normally’ from make.powerapps.com, and connected to a Power BI report at a later stage. See Part 1 of this article series for further details.

https://www.linkedin.com/embeds/publishingEmbed.html?articleId=8322741095634091622&li_theme=light

Congratulations! You are now able to write data back to your data source and display it live in your report!

Summary

In this article you were taught how to make your Power Apps data write-back entries show up in Power BI, and how to trigger visual refreshes of your Power BI reports with Power Apps to display DirectQuery data live without the need for any refreshes.

Do you have any interesting use cases for this feature, or ideas you want sparring on? Feel free to reach out.