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/

Curbal on Youtube

2 thoughts on “Use Power Apps to write into Azure SQL Database

  1. Hey Erik, great read! This tutorial was really easy to follow. Being a fellow tech blogger myself, I also really appreciate how organized and well-formatted everything was – it definitely made the content much more digestible overall. Keep up the awesome work!

    Like

    1. Thank you Rohan!

      Like

Leave a Reply to Rohak Jain Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close