Power BI integrates Power Apps to easily allow users to enter any information in reports

Power BI report is very powerful, but usually its information is unidirectionally transmitted. The report maker transmits information to the user through the data on the report. The user can interact with the chart on the report, but cannot add information to the report. This is also a feature that many people look forward to.

Although Power BI itself does not support allowing users to enter data, this requirement can also be easily achieved by using another application in the Power family: Power Apps.

Take the following report as an example, showing the sequential growth data for each product:

If this report is sent to the leader, after the leader sees it, he may make suggestions for dealing with some products. How to add an input box in Power BI for the leader to input text?

The following will use Power Apps to achieve this requirement. The prerequisite for this method is that you need to have a Onedrive for business account and have a Power Apps license.

The basic principle is that the information entered by the user is written back to OneDrive through Power Apps, and then Power BI connects to the data on Onedrive and presents it on a visual report.

Ready to work

First, create a table on Onedrive for business. Here I named it "Remarks Table". It has two columns: product name and remarks. In order for Power Apps to recognize it, this data needs to be converted into a smart table, and the style can be applied:

There is no data in the remarks column here, and it is prepared for the following write-back.

Then use Power BI to connect this table on OneDrive, and after importing it into the model, establish a relationship between this table and the product table in the original model:

Then you can put the "Remarks" column in the remarks table into the previous table:

No remarks have been added yet, so this column is empty.

Create apps with Power Apps

Add the Power Apps visual on the canvas:

Put the field "Product Name" in the product table, and after the system is configured with the environment, click "New"

A long list of URLs will pop up later, click OK to enter the Power Apps cloud, and after entering, delete the existing layer "Gallery1":

Then there is a blank canvas, and we start building the application again.

1. Insert > Form > Edit

2. Connect to the data source

Click on the newly created edit box, then in Properties > Data Sources > Connectors, select OneDrive for Business

Then select the pre-built "Notes Sheet" in OneDrive.

3. Add fields

Add both fields from the Notes table.

Then two field boxes appear on the canvas.

4. Get Power BI context with formulas

In order for the form created above to get the context of Power BI, you also need to write a formula, click on the layer Form1 on the left, select Item in the drop-down box above, and then enter the formula:

LookUp(Remarks table, ProductName=First(PowerBIIntegration.Data).ProductName)

Its logic is to look up the information of the Notes table by the product name in the context of Power BI.

5. Add button

After the information is entered, a button action is needed to submit the information, so a button needs to be inserted on the canvas:

Regarding the text and format of the button, you can set it in the format panel on the right:

Then set the operation properties of the button, and enter in the formula bar above:

SubmitForm(Form1)

6. Application Design

You can insert a title above the canvas to look more beautiful, just click Insert > Label above.

In addition, a text box is inserted here to prompt the user for the correct action.

7. Save and publish

Click "File" and choose to save as to the cloud:

Then click Save in the lower right corner. At this point, the application is created. Return to the Power BI report page to see the application.

Test the effect of the application

First select a product, and then make a note for the product. For example, select the "VR glasses" with the largest month-on-month decrease, and enter in the note:
Ask the marketing department to explain the reason and come up with a reverse plan!

Then click "Confirm", the note information will be written back to Onedrive.

At this time, this text will not appear in the table immediately, because the report has not been refreshed. Refresh the report first, or only refresh the "remarks table"

Then the data in Onedrive is imported, and the text you just entered will appear in the remarks column of the table:

After the report is sent out, users can directly enter comments on the Power BI page, and other people can see their opinions as long as they refresh the report. Isn't it very practical?

Summarize

The above is a classic application scenario of Power BI integrating Power Apps. Here is how to create an application. You can continue to optimize the specific details. If you use the direct connection mode, you can set automatic refresh, use bookmarks to pop up/hide the Power Apps application window, etc. . Friends who are interested in Power BI can try it out!

Microsoft Most Valuable Professional (MVP)

The Microsoft Most Valuable Professional is a global award given to third-party technology professionals by Microsoft Corporation. For 29 years, technology community leaders around the world have received this award for sharing their expertise and experience in technology communities both online and offline.

MVPs are a carefully selected team of experts who represent the most skilled and intelligent minds, passionate and helpful experts who are deeply invested in the community. MVP is committed to helping others and maximizing the use of Microsoft technologies by Microsoft technical community users by speaking, forum Q&A, creating websites, writing blogs, sharing videos, open source projects, organizing conferences, etc.

For more details, please visit the official website

Click-to-create low-code apps for process modernization