I had the pleasure last week of presenting together with Bogdan, at the Devon and Cornwall Power BI user group, where we showcased the synergy of Power BI with Power Apps, looking from both angles. You can watch the recording here.
To make the session more challenging, we chose to base our showcases on a Fabric data warehouse rather than the usual direct query method, which typically uses Dataverse tables. This posed a challenge because of a key difference between the two data sources. Dataverse tables have a default enforced primary key (the table rows have a unique identifier defined as GUIDs), while a Fabric data warehouse does not enforce primary keys. This distinction matters because the Patch function in Power Apps can only be used, if the destination table includes a primary key.
In this case there are two possible solutions to perform the write back:
Before we go into the details, the overarching context of the showcase, was a Power BI report which has an embedded Power Apps, both leveraging data from a Fabric data warehouse.
From within the Power Apps canvas, we can start the creation of a Power Automate flow, by using the left menu. Click on the more options icon and then Power Automate.
Since we don’t want to use an existing Power Automate flow, click on 'Add Flow,' then choose 'Create New Flow,' and finally select 'Create from Blank' or browse through the available templates.
Clicking on it opens the Power Automate editing canvas within the Power Apps editing window.
The flow will consist of three steps:
This step was added by default when the initial canvas was opened, however this is a vital step. In this step we will define the input parameters coming from Power Apps, that will be used when executing the SQL statement.
Three inputs are sourced from the app, while an additional input is retrieved from the app's data gallery.
For each field, I have included the corresponding data type, as this is very important. Selecting an incorrect data type will cause the SQL statement execution to fail.
When adding a new input parameter, first select the data type, then provide a name and optionally a description of the input.
NOTE
If an input parameter needs to be deleted, but is used in a subsequent step, you need to remove the respective usage reference from the other steps before you can delete the parameter from the trigger.
To write data to a Fabric data warehouse, you’ll need both the SQL connection string and the table name. We will then write the SQL statement and introduce the dynamic content provided through the trigger.
This is how the code looks:
UPDATE ActualWonders
SET
ServiceDate = 'InputValue1',
ServiceAgent = 'InputValue2',
Status ='InputValue3'
WHERE
OrderNumber = 'InputValue4'
The final action sends feedback to PowerApps, indicating whether the action executed successfully or if it failed. This is done by adding the same action twice as parallel steps, with one action defined to run if the SQL execution is successful and the other action to run only if the SQL execution fails or got timed out. This configuration is managed by adjusting the Configure Run After settings under the options menu (three dots) for each action. You can also see below that the direction arrow looks different for the second action, which will run only if the execution fails.
Creating the flow will add it into the Power Apps context menu. The next step is to invoke the flow in the Power Fx formula, corresponding to the Assign Order button’s OnSelect property. The important thing to remember is that, when we invoke the flow, the order of the input parameters defined in Power Automate matters, however you will get IntelliSense support for the parameters. The invoke syntax defines the function as 'FlowName'.Run().
The Power Automate flow will execute within a few seconds, but due to the Direct Lake connection, you will need to consider a delay of at least 20-25 seconds (recommendation is to consider 30 seconds) until the data is visible in Power BI. To signal the waiting time to users, we can use an SVG or the built-in modern Spinner control.
NOTE
In order to try this out, you need to first allow the new modern controls features from Settings ➡️ Updates ➡️ Moden controls and themes
As we look at it for a longer time, to make it more engaging for the users, we will use 4 spinners, each providing a different message.
For each spinner added, we will open up the Visible property and define a variable which will be used later to display/hide the spinner. Afterwards we will define the text to be displayed, but also the spinner size.
In addition, you see that all spinners are positioned within a container, so that we can also use a variable to create a blur effect.
After they are added, we will go back to the Power Fx formula captured in the OnSelect property of the Assign Order button. The formula will use a timer and play with the visibility of each spinner through those defined variables.
Now let's look at the full output:
With this integration, Power Apps and Power BI work together to streamline data management, bringing near real-time insights and updates directly to your analytics environment. Whether you're using the Fabric data warehouse for scalability or seeking responsive Power BI visuals, these steps help you make the most of your setup. Ready to take your Power Platform skills further? Start implementing, and watch your data flows transform!