Use Power Apps to write into Azure SQL Database
For a while, I wanted to create this demo, for which I’ll be using Power Apps to insert data into an Azure SQL database and show the results in our report. In this example, I’ll insert and update some records, refresh the page, and refresh the dataset using Power Automate. There will be two examples: One with DirectQuery and one with Imported data. For both, we’ll be using the same database table.
Create a database table
First, I connect to an Azure SQL Database (so I won’t need a Data Gateway), select my database and create a table. After that, I insert three rows of data.
CREATE TABLE [dwh].[TargetPerConsultant](
[RowID] [int] IDENTITY(1,1) PRIMARY KEY,
[Firstname] [varchar](50) NULL,
[TargetAmount] [smallmoney] NULL
) ON [PRIMARY]
GO
INSERT INTO [dwh].[TargetPerConsultant] (FirstName,TargetAmount)
VALUES ('Arthur',1200),
('Erik', 1000),
('Tom', 900) It’s essential here to create an ID column and make it the Primary Key, otherwise, Power Apps won’t let you write back to the database. With the IDENTITY(1,1), I’ll ensure it’s an autoincrement value. This is what the table looks like right now:
Table with Consultant name and the target amount
And with that our first part is done, lets’s start with Power BI!
Creating a Power BI Report
In Power BI I make a connection to my SQL Server database, and use the DirectQuery option:
Select DirectQuery as the data connectivity mode
Then I select my required table and load the data.
Select the required table
The next thing to do is to show some visuals to make the data visible:
Create some visuals
And now it’s time for the fun stuff! By using the Power Apps visual, we’re going to create our app to write to the database:
Add the Power Apps visual
Before we can move to Power Apps, we must add the fields that we want to use in Power Apps to the fields bucket. I’ll put all my columns there and then we can start building!
Selecting the required fields
You get the option to choose an app you already created or create a new one. If you create this app in Power Apps, you don’t get the PowerBIIntegration data source. This one is used to refresh the page and make our changes visible immediately. So that’s why we now select Create new. You’ll get a message that you will be sent to the browser and to the Power Apps Studio.
Power Apps Studio
The screen that will open looks like this:
Power Apps Studio
On the left you see the menu, next to that you’ll find the Tree view, in the middle a canvas that’s already been created for you and on the right some details. Now, first we going to create a connection to our database, by using the Data menu item.
By clicking on Add data, you can add a SQL Server to our project:
Add SQL Server as Data Source
We’ll be using the same SQL Server as where our Target per Consultant table is located. After connecting you can select the tables you want to use. After connecting we can start building our Power App!
Select the required table
First, I create a new page called Scrollable Canvas, delete the screen that Power Apps already created for us, and add a Gallery to our new page.
Create a new screen with a gallery
Now we need to adjust some properties.
Data source:
Connect the data source
Gallery Layout:
Change the layout of the gallery
Fields, so we have the target Amount as subtitle:
Change the fields to we have the Firstname and the Targetamount
I’ll also give my page a title and rename the page from Screen # to Overview:
Change the title and page name
Now we create a new Scrollable Canvas and instead of adding a gallery, we’ll add an editable form:
Add an editable form to the canvas
Now we will have to connect our data source again, and select the fields we want to edit:
Add the required fields
After that, select the form, and change the formula to Item = Gallery#.Selected. The field will be filled with some data. Now if you select another consultant on the Overview page, you’ll see that information. But before that, we need to add some actions.
Gallery2.Selected
On the Insert menu, go to Icons and select the < Left icon:
Select the Left Icon
Move it to a nice spot on your canvas, we’re going to use this button to navigate back to the overview page. With the icon selected on your canvas, go to Action, Navigate, and select the Overview page:
Put the navigation option to Overview
Now, if you’ll use ALT+Left mouse click on the button, you’ll navigate to the overview page. Now lets rename our new page to EditForm and select the > right icon on your overview page to change the navigation action of that one:
Navigate to EditForm
Now can easily navigate through our application:
Navigate through the screens
On our EditForm screen, we need to add a button, so we can save the changes we made in our table. For that, go to Insert, Button, and you’ll get a nice button. Let’s give it the text “Submit”. With the button still selected, give it the following formula:
SubmitForm and Refresh Power BI
This will submit our form to SQL Server and refresh the page as well. Now select the form, and create the following formula:
Navigate back to the Overview
If the form is successfully processed, we will navigate back to the Overview page. On the Overview page, we need to create another icon, with a Plus sign, which will be used to create an empty form in our EditForm screen. Give this button the following formula:
Create an empty form
Now when we use the Play Icon in the right top corner or by pressing F5, we can test our application:
Adding data to SQL using Power Apps
Our database is updated, so it works!
Updated SQL Database table
Now we must save our Power App and make it available. After doing that, for some reason, my PowerApps visual in Power BI still stays on the same screen. So, I’ll remove that one. That way I get the option to choose an already created one:
Select our newly created Power App
You will now see your Power App inside your Power Bi report and you’ll be able to interact with it! In the Power BI Desktop, you will still have to press the refresh queries button to see your changes, but if you publish your report and watch it in the Power BI Service:
Power BI report with Power App embedded making changes to the data
Using Import Data
If you use Import Data mode instead of DirectQuery, you need to find a way to refresh the dataset before refreshing the page. For that, we’re going to use a button that connects to a Power Automate flow. But first, I create the same report using Import mode and publish my report to a workspace. Then I’ll make some changes to the Power App we created before.
In PowerApps, go to Action, Power Automate, and then Create New flow:
Creating a new Power Automate flow within Power Apps
And then select Create From Blank. Now in the next step, search for Power BI Refresh. In the list you’ll find the Refresh a dataset action:
Select Power BI Refresh a dataset
When you select that Action, you’ll have to select the Workspace and the Dataset you want to use in this action. That’s the reason why I already published my report to the workspace:
Filling the details for the dataset refresh
After saving, you’ll be redirected back to Power Apps, where we will update our Submit button:
Change the function for the button to refresh a Power BI Dataset
Instead of using the PowerBIIntegration.Refresh, we will run the PowerApp -> Refreshdataset. Because a refresh of the dataset can take some time. We’ll create an extra button on our Overview screen, to refresh the visual on the page and add the PowerBIIntegration.Refresh there:
Power BI Refresh button
Now we can save and publish this version of the Power App and add it to our report. With this result:
Using Power Apps and Power Automate within a Power BI report to update the data and refresh the data set
Now, since our dataset is very small, the refresh doesn’t take that long, which is nice for our demo:
Fast refreshes using Power Automate
But be careful, if you’re not in a premium (per user, or per capacity) workspace you cannot reload that many times, also not via PowerAutomate.
Ending
Hopefully, you’ll find this information useful and I’m curious to know which scenarios you can think of where this would be helpful!
Take care.
Thanks to these sources:
https://www.cittabase.com/refresh-power-bi-dataset-from-report-using-power-automate/