Power BI Comments Form with Power Apps

By: Hristo Hristov   |   Updated: 2021-06-01   |   Comments (1)   |   Related: > Power Apps

Problem

When you use Power BI to visualize datasets, you may want to provide your users the option to leave comments either for the whole report, for a certain table or even for a specific data point.

Solution

To implement this scenario, you can embed a Power Apps canvas app into a Power BI report. This tip will show you how.

SQL Server Database Setup

Let us first examine the example setup. You can follow these steps as a training exercise or read through the whole tip first and then adapt it top your needs. For this example, I am using the AdventureWorks LT 2019 database. You can download it from here. To ensure we have a place to store our comments, we must create a new table in the database:

CREATE TABLE SalesLT.Comments (
    CommentID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT,
    Comment VARCHAR(2000),
    Author VARCHAR(255),
    CommentTimestamp DATETIME,
    FOREIGN KEY (ProductID) REFERENCES SalesLT.Product(ProductID)
);

For this new table, at the very minimum we must have an ID column and columns for the comment text, author name and date / time the comment was made. In this case we will be leaving comments for products from the SalesLT.Product table so naturally we also need a foreign key on ProductID. In this way we can relate every product to one or many comments later.

Power BI Report

Open Power BI desktop. From the blank canvas, click Import data from SQL Server. The configuration should look like what is on this screenshot (if using localhost):

I am using DirectQuery, but the steps here apply for Import as well. Next, in the navigator, import only SalesLT.Comments, SalesLT.Product and SalesLT.ProductCategory:

AAfter that add some visuals to the one page in the report. The field from the dataset that we want to use in the comments app may or may not be present in a visual. So, I have made this page that displays average category prices (1), a table with products (2), the comments table (3), and a slicer for category and product (4). After that, use the Power Apps for Power BI icon and add the Power Apps placeholder (5) to the page too:

Next, select the Power Apps visual you just added and drag the Comment field from the SalesLT.Comments table:

Here it is important to differentiate between Create new and Choose app. Create new will ensure you have the PowerBIIntegration control in your canvas app allowing you to access the table from the dataset without explicitly making a connection to it. On the other hand, if you pick Choose App, a list of all your apps will appear along with a dropdown for environment selection allowing you to embed apps from various environments in your tenant. In short, with Choose App you may embed not only a comments app, but any other app that makes sense to have inside the report. Additionally, there is no limit on how many apps can be present on a page in Power BI, but you have to be mindful of the impact on the loading time. At this point we are ready to head to Power Apps and make the comments app.

Power Apps Comments App

Click Create new (unless you have an existing app), then click Go to PowerApps studio. A pop up may appear warning you that you are navigating away, so agree and continue. This will result in the Power Apps studio opening in your browser with a blank app in the default environment. By default, you will see a Gallery control added and the notable PowerBIIntegration control:

Although you can use this control to populate a gallery or a text field, or even to join to local collections, you cannot use it to write back to your dataset. So, the first order of business is to also make an explicit connection to the database and the Comments table. Do this by going to the datasources tab, click Add data and pick the SQL connector. You may have to configure a connection prior to doing this or by clicking Add a connection. In this case, I have a preconfigured connection to my localhost. When you pick a connection to a database, Power Apps will also prompt you to choose at least one table from the target DB, so pick SalesLT.Comments and SalesLT.Product. Be advised at this point you are using a premium connector that will require an additional per-app or per-user license in case your target users do not have it yet.

Now apply the following configurations:

Control - Drop down

• Property - Items
• Explanation - This setting will populate the dropdown automatically with values from the Product table. Beware dropdowns are limited to 500 items. In future tips we will explore how to circumvent that.
• Formula - 'SalesLT.Product'.ProductID

Control - Text Input

• Property -EnableSpellCheck
• Explanation -These two settings ensure a good user experience while writing the comment inside the box. If you keep the default Mode setting (Single line), there will be no scrollbar for longer comments.
• Formula - true

• Property - Mode
• Formula - Multiline

Control - Button

• Property - OnSelect
• Explanation - By using the Patch formula, we write the comment back to the database. For the Author and Timestamp values we use the built-in functions accordingly. With the Refresh function we ensure the data source will be refreshed and the comment will appear immediately in the gallery. The Reset function will reset the comment input.

Patch(
    'SalesLT.Comments',
    Defaults('SalesLT.Comments'),
    {
        ProductID: ProductIdSelector.Selected.ProductID,
        Author: User().FullName,
        CommentTimestamp: Now(),
        Comment: CommentBox.Text
    }
);
Refresh('SalesLT.Comments'); Reset(CommentBox)

Control - Gallery

• Property - Items
• Explanation - The gallery was added automatically when the app was created. However, the Items property must refer to the actual data source and not the PowerBIIintegration control
• Formula - 'SalesLT.Comments'

Note the gallery has an embedded object called Template. It allows you to configure once every row that will be created in the Power Apps interface for every comment coming from the database. You may consider adding some color, border colors or other slight visual improvements. By adding more labels into the Template, you can show other relevant attributes of the current item, such as the author and the timestamp. The following screenshot shows an overview of all configurations. At this point the overall look of the app should resemble something like this:

As soon as you save and publish the app in the Power Apps studio, the Power Apps visual in Power BI will refresh automatically and visualize the newly made app in the report.

Note that the comment will not appear immediately in the comments table during report design time or runtime. The comment you added will only appear once the dataset itself has been refreshed according to the refresh configuration in the Power BI service. However, the gallery in the Power App ensures that comments are shown immediately.

Enhancement – Option to edit a Comment

Let's look at how to enable the authors to edit their comments. First, substitute the label control for the comment text with a text input control. From the icons’ menu, add three icons: a pen icon for editing, a cross icon for canceling and a check icon for confirming. Assign the following configurations:

Control - Screen1

• Property - OnVisible
• Explanation - We are setting a helper Boolean variable to track the state of a comment being edited.
• Formula

Set(
    editMode,
    false
)

Control - Pen icon

• Property - Visible
• Explanation - Ensure the icon is displayed only for the current user’s comments.
• Formula

If(
    ThisItem.Author <> User().FullName,
    false,
    true
)

• Property - OnSelect
• Explanation - Change the value of the state tracking variable to whatever is the opposite, e.g. if currently true, set to false.
• Formula

Set(
    editMode,
    !editMode
)

Control - Text input

• Property - Default
• Formula

ThisItem.Comment

• Property - DisplayMode
• Explanation - Note all text input fields will go in Edit mode in the whole gallery, However the cross and check icons will be available only for the currently selected item.
• Formula

If(
    editMode,
    DisplayMode.Edit,
    DisplayMode.View
)

• Property - Fill
• Explanation - Highlight the background of the text box.
• Formula

If(
    !editMode,
    RGBA(
        220,
        220,
        220,
        1
    ),
    RGBA(
        255,
        255,
        255,
        1
    )
)

Control - Check Icon

• Property - Visible
• Explanation - If the state is set to edit and the ID of the current item equals the ID of the selected item in the gallery, show the submit icon.
• Formula

If(
    And(
        editMode,
        ThisItem.CommentID = Comments.Selected.CommentID
    ),
    true,
    false
)

• Property - OnSelect
• Explanation - Clicking the icon will save the change by overwriting the specific comment in the database. The LookUp function ensures we write back to that comment.
• Formula

Patch(
    'SalesLT.Comments',
    LookUp(
        'SalesLT.Comments',
        CommentID = Comments.Selected.CommentID
    ),
    {Comment: CommentText.Text}
)

Control - Cancel Icon

• Property - Visible
• Explanation - If the state is set to edit and the ID of the current item equals the ID of the selected item in the gallery, show the cancel icon.
• Formula

If(
    And(
        editMode,
        ThisItem.CommentID = Comments.Selected.CommentID
    ),
    true,
    false
)

• Property - OnSelect
• Explanation - Clicking the cross (cancel) icon will remove the edits and reverse the state of the editMode variable.
• Formula

Reset(CommentText);
Set(
    editMode,
    !editMode
)

Here is how the app should look after these configurations:

On the left it is "in edit mode" and on the right "not in edit mode".

Now you can save and publish this app and it will be available in your Power BI report providing the capability to add end edit comments.

Next Steps

• Limitations of the Power Apps visual
• Power Apps Patch function
• Controls and properties in Power Apps
• Check out all of the Power Apps tips on MSSQLTips.com

About the author

Hristo Hristov is a Data Scientist and Power Platform engineer with more than 12 years of experience. Between 2009 and 2016 he was a web engineering consultant working on projects for local and international clients. Since 2017, he has been working for Atlas Copco Airpower in Flanders, Belgium where he has tackled successfully multiple end-to-end digital transformation challenges. His focus is delivering advanced solutions in the analytics domain with predominantly Azure cloud technologies and Python. Hristo's real passion is predictive analytics and statistical analysis. He holds a masters degree in Data Science and multiple Microsoft certifications covering SQL Server, Power BI, Azure Data Factory and related technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips