Updating Microsoft Fabric Warehouse with Power Apps visual in Power BI

One of my recent explorations with Microsoft Fabric was integrating Power Apps with a Fabric Warehouse—both in a standalone Power Apps app and as an embedded visual within a Power BI report to enable writeback. My goal was simple: to enable Power Apps to display and update records from a table in the Fabric Warehouse. Initially, I turned to the three-screen template apps to get started, however; while it displayed the records, it failed to update them. This led me to dive deeper into how Fabric Warehouse differs from other SQL data sources when it comes to Power Apps and to find a workaround. In this post, I will share my findings and the workaround I used. I have also posted a video to my YouTube channel on this topic.

Note: For all examples in this post, I’ll be using the dimProductCategory table from the AdventureWorks sample database.

---

How Power Apps updates SQL data sources

Setting up Power Apps to work with Azure SQLDB or SQL Server on-prem is quite straightforward. To build an app that displays records from a table and allows for editing or adding new records, you can simply use the Power Apps template wizard to create a three-screen app that works seamlessly across platforms. These apps use a Gallery and two Form controls to display and edit records, with the SubmitForm function saving any changes back to the data source. As you gain more experience with Power Apps, you might opt to graduate from Forms and instead use free-form display controls like labels and text boxes to show records, while using a button with the Patch function to update them. Let’s explore both methods.

Three-screen Template App with SubmitForm

The quickest way to connect Power Apps with a table in a SQL data source (SQL Server, Azure SQLDB, or Dataverse) is by using the three-screen template app wizard in Power Apps Studio. You simply select your data source, and Power Apps Studio handles the rest.

For example, the following shows a such n app pointing to the dimProductCategory table in my Azure SQLDB data source:

With the Edit Screen looking like this:

If you examine the formula behind the Save icon (checkmark) in this app, you will see that all that the app needs to do to save the changes to the backend is call the SubmitForm function. SubmitForm works with a Form which is already on the screen. A form has two key properties: DataSource and Item.

• DataSource specifies where changes will be saved.
• Item determines what populates the form fields.

In this template app, the form gets its values populated from the selected item from the Gallery visual on the first screen. Its data source is set to the same table that I pointed the wizard to when I created the app. Although simple, this setup is fully functional and serves as an excellent starting point.

Note: If the ultimate goal is to embed this app in Power BI to enable writeback, the app must be built from Power BI service to interact properly with Power BI.

Free-Form App with Patch Function

After moving beyond the three-screen template apps with Forms, you can use Labels/Text Box controls to display data and collect input from users, while using the Patch function to save the data to the data source. Below is an example of the Edit screen in such an app.

The following shows a sample code that runs when a user clicks on the Update button:

UpdateContext({ThisItem: LookUp(DimProductCategoryForPowerApps, ProductCategoryKey = Int(inpProductCategoryKey.Text))});


Patch(
    DimProductCategoryForPowerApps,
    ThisItem,
    {
        EnglishProductCategoryName: inpEnglishName.Text,
        SpanishProductCategoryName: inpSpanishName.Text,
        FrenchProductCategoryName: inpFrenchName.Text
    }
);

The first line of this code uses the LookUp function to find the record that needs to be updated. The first parameter of LookUp is the data source, while the second parameter specifies the criteria used to locate the record.

Once you have identified the record to be updated, you can use the Patch function. The first parameter is the data source, the second parameter is the record to be updated, followed by a list of the fields and their corresponding values.

Behind the scenes with SubmitForm and Patch

Both SubmitForm and Patch execute a specific system stored procedure called sp_executesql to update the data. In fact, it seems like SubmitForm uses Patch behind the scenes itself which will call the stored procedure. The Power Apps developers do not have any control over this command, nor do they see it, it is auto generated in the background. You can capture this command by running a trace on SQL server or use Azure Data Studio for Azure SQLDB, or through Query Insights for Fabric Warehouse.

Below is an example of this command:

exec sp_executesql N'UPDATE [dbo].[DimProductCategoryForPowerApps] SET [EnglishProductCategoryName]=@p0 OUTPUT [INSERTED].[EnglishProductCategoryName],[INSERTED].[SpanishProductCategoryName],[INSERTED].[FrenchProductCategoryName],[INSERTED].[ProductCategoryKey],[INSERTED].[ProductCategoryAlternateKey] WHERE [ProductCategoryKey] = 1',N'@p0 nvarchar(13)',@p0=N'BikesTest'

Here is a breakdown of the above code:
UPDATE: Specifies the table to be updated.
SET: The field to be updated set to a variable (@P0) defined at the end of the code.
OUTPUT: Lists the fields to be read back once the update is complete.
WHERE: Defines the criteria for updating specific records.
The variable defined as @p0: Represents the value being assigned.

For more info on this function see this Microsoft Learn reference.

Why SubmitForm and Patch Don’t work with Microsoft Fabric Warehouse

Whether you use the three-screen template apps with Forms that use the SubmitForm function or a free-form data entry app with the Patch function, neither approach will successfully update records from a Fabric Warehouse table. Below is a screenshot of the app displaying the error message:

“Network error when using Patch function: An error occurred on the server.“

I knew that the network error was a false error because the app could display the records, meaning it could reach the data source. To investigate further and capture the update statement, I looked at QueryInsights in the warehouse by running the following code in the Fabric Warehouse Explorer:

SELECT * FROM queryinsights.exec_requests_history 
WHERE start_time >= DATEADD(MINUTE, -3000, GETUTCDATE())
AND command like '%sp_execute%'

The update query appeared exactly as I expected with the sp_executesql command as previously shown. I then took the query and ran it directly in the Fabric Warehouse Explorer, which revealed the actual error:

“OUTPUT is not supported.“

There was the clue! While Fabric Warehouse supports sp_executesql, it does not yet support the OUTPUT clause.

Workaround for Updating Microsoft Fabric Warehouse with Power Apps

Since sp_executesql is not an option and both Patch and SubmitForm rely on it, I needed to find an alternative. Fortunately, earlier this year, Power Apps added support for calling Stored Procedures directly.

I created a stored procedure in my Fabric Warehouse to update the table as follows:

CREATE PROCEDURE [dbo].[sp_UpdateProductCategory]
@ProductCategoryKey int,
@EnglishProductCategoryName [varchar](8000),
@SpanishProductCategoryName [varchar](8000),
@FrenchProductCategoryName [varchar](8000)
AS
BEGIN
    UPDATE dbo.DimProductCategoryForPowerApps
    SET 
        EnglishProductCategoryName = @EnglishProductCategoryName,
        SpanishProductCategoryName = @SpanishProductCategoryName,
        FrenchProductCategoryName = @FrenchProductCategoryName
    WHERE ProductCategoryKey = @ProductCategoryKey;
END

In Power Apps, when you create a SQL data source connection, you get access to the stored procedures within that data source. Instead of selecting a table as the source, you can select a stored procedure as the source then you can call the stored procedure in your Power Apps code by typing the name of the data source followed by a dot.

Note: Even though I will be using the stored procedure to do an update, it is still set up as a data source.

Below is a screenshot of my free-form app:

The following shows the code for the Update button:

Clear(ColNewSQLReturn);

ClearCollect(
  ColNewSQLReturn,
  WarehouseForPowerAppsTest.dbospUpdateProductCategory(
    {
      ProductCategoryKey: Int(lblProductCategoryKey.Text),
      EnglishProductCategoryName: inpProductEnglishName.Text,
      SpanishProductCategoryName: inpProductSpanishName.Text,
      FrenchProductCategoryName: inpProductFrenchName.Text
    }
  )
);

Note that it is considered best practice to use Clear() before loading data into the collection in Power Apps because it ensures the collection is emptied of any previous data.

Putting it all together: Enabling Writeback to Fabric Warehouse using Power Apps Visual in Power BI

For the Power Apps visual to successfully interact with a Power BI report, you must first publish the Power BI repot to Power BI service. Then, edit the report from Power BI service and add the Power Apps visual to it. From there, you can create either create a new Power Apps app or use one that you previously created from Power BI service. The following image shows the correct order of the operations. Please refer to my previous post where I explain why the order of operations matter.

When you follow these steps, if the semantic model behind the Power BI report is Direct Query or DirectLake, the automatic page refresh triggered by the Power Apps visual will display the latest updated values in the Power BI report. It’s important to note that even when the model is in Import mode, you can still update the data; however, the changes will not be reflected until a model refresh (which is different from a page refresh) is completed. See notes at the end of this post to learn more about the Power BI storage modes.

To demonstrate the steps, here is an example. I created a simple report from dimProductCategory table.

Then clicked on Edit and added Power Apps visual to the screen.

Next, I selected the fields and clicked on “Create New” in the Power Apps visual. This action creates a one- screen app featuring a Gallery control. The Gallery’s Items property is set to PowerBIIntegration.Data ,which is exactly what we want.

Since I don’t plan to use the Gallery with a Form (as SubmitForm does not work with Fabric Warehouse), I made it small and invisible because it provides a convenient way to read data from Power BI and prepopulate the free-form (pun intended :)) Text Box controls based on the item the user selects in Power BI.

The following screenshot shows the finished product:

To prepopulate the Text Box controls with selected values from Power BI, I set the Default setting of each Text Box with the following pattern: Default = Gallrey1.Selected.FieldName

Even though the gallery is hidden, it still has a Selected item that is determined by the user’s selection in Power BI.

Now that the Text Boxes have the selected values displayed, I set the OnSelect property of the button to the code I shared earlier in this post. The only difference this time is that, since this app is embedded in Power BI, I wanted to trigger an automatic page refresh. Therefore, I added PowerBIIntegration.Refresh() at the end.

Note: If you get an error message or see squiggly red lines when trying to add PowerBIIntegration.Refresh(), it means that you have not followed the correct order of operations correctly. Please refer to my post here.

A few notes about the automatic Power BI page refresh

It is important to remember that a Power BI page refresh is different from a Power BI semantic model refresh. The PowerBIIntegration.Refresh() simulates a user clicking the page refresh button, which forces all visuals to refresh and rerun.

• If the semantic model is in Import mode, the visuals will not display any updated values until the model is refreshed. This might be acceptable and preferred in some use cases.
• If the model is in DirectQuery mode, you will see the updated values immediately.
• If the semantic model is in DirectLake mode, you will see the updated results, but there may be a delay of 30 to 60 seconds before they appear.

DirectLake, the newest storage mode for Power BI, combines the advantages of Import and DirectQuery modes. It offers performance comparable to Import with almost near-real-time benefits of DirectQuery. When data is updated in the Warehouse, it takes 30-60 seconds for the results to be reflected in queries though the default or a custom Power BI semantic model that is in DirectLake model; therefore, the automatic page refresh may still not show the changes immediately. If this delay is not acceptable, you can use a model with DirectQuery storage mode through the SQL Endpoint of the Warehouse.

Considerations for Updating a Fabric Warehouse Table from PowerApps

While using Power Apps to update data in a Microsoft Fabric Warehouse is indeed possible, it is important to recognize that a Fabric Warehouse is not designed for OLTP workloads. Instead, it serves as a data warehouse and reporting system. Ideally, updates to business fields would occur at the primary business data source before the data is ingested into the warehouse. The use case shown in this post can be used in edge cases where updates are needed at the final layer. Whether this approach is advisable depends on the specific context.

Additionally, it’s important to understand that since the Fabric Warehouse stores data in delta (parquet) files, small incremental updates will generate new parquet files while modifying the Delta log folder. If there are numerous individual updates or inserts over time, the number of small files will increase significantly. This growth must be managed to achieve best performance over time. For more information on this topic, see this.

Related Video

Check out my video on my YouTube channel on the same topic to see some demos: Updating Microsoft Fabric Warehouse with Power Apps visual in Power BI