PowerApps and PowerBI Integration

I recently worked on Power Apps, an app making tool by Microsoft. The functionality which I wanted to achieve was to select the data from a Power BI report and populate and filter the App based on the user selection.
The data source we used in this project is MS SQL Server.

For Example : The user will select an item from the slicers given in Power BI and according to the same selection , the form will get filtered/populated. The details further inputted by the user will get stored in the SQL Server.

The function used to achieve this was the “Patch” function ( To send the values back to the SQL server ) and “PowerBIIntegration” ( To take in the values selected from the PowerBI report, in our case, the slicers).

Patch ( Data source, Base Record , Records to be changed or updated )

This is exactly what you will find in the official documents of Microsoft PowerApps.
Data Source : Your main sql server table
Base Record : Since we were updating on the same table it will be kept Defaults(tablename)
Records : this was little tricky to get hold of. Several things kept to be in mind which I am listing down :

1. If your field type is a number / int another function called “Value” will be used since the columns are needed to undergo an implicit conversion.
   The syntax will be : Value(TextInput.Text)
2. The fields which needs to be integrated from Power BI will include the function discussed above “PowerBIIntegration”
   The syntax will be : First([PowerBIIntegration].Data.TextInput)
   First is used here because it selects the first value from the slicer (basically the value you choose) so by default whatever is displayed first on the slicers will be shown in such fields.
   you can additionally disable these fields since you do not want the app use to change these by going to the Properties window to your right and selecting Disabled from the Display Mode.
3. The Patch function will be applied on the ON SELECT property of a Button ( probably , submit or a save button). You can choose the property from Top Left , 3rd row, first dropdown.

Properties of the selected text fields/labels/buttons etc.

4. It is important to refresh our app after we have filled the fields since we are sending it back to our database hence we will use the function called PowerBIIntegration.Refresh()

5. You can also keep DropDowns on your screen by Inserting the respective from the INSERT → INPUT → DROPDOWN on the Top Left.
For keeping some values in the dropdown, you need to manually write them by going to the ITEMS property and setting it to [“x” , “y” , “z”] so that the user can have 3 options to choose from.

6. The toggle button can also be selected in the same way.

This is whole of the code we used to achieve the following :

1. Send the user input data back to the database
2. To integrate Power BI values to the Power Apps
3. To refresh the App

was :

Patch(‘[dbo].[TableName]’, Defaults(‘[dbo].[TableName]’),
{ColumnName_1 :TextInput_1.Text},
{ColumnName_2:First([@PowerBIIntegration].Data.TextInput_2).TextInput_2},
{ColumnName_3: Value(TextInput_3.Text)},
{timeStamp:Now()},
{userId: User().Email},
{userName: User().FullName},
{ColumnName_4: Toggle1.Value},
{ColumnName_5: TextInputDD.Selected.Value},
PowerBIIntegration.Refresh();

The options of ColumnName_x will come on its own when you start typing, they will be the ones used on your sql server table.

The TextInput_x are the names you have chosen for your text fields in that screen.

Functions like Now() gives the current date and time
User().Email gives the current users email ID, User().FullName will give their full name.
DD means dropdown and Toggle1 is the toggle buttons name you have chosen.

Always remember, the patch function can only include fields which are there on the screen where you are writing the function.

Stay tuned for more articles on how to select a layout / connecting different data sources in the same screen/ more properties and functions!
Please do post any questions you have regarding this.