PowerApps In PowerBI: passing information from a source table to a writeback table, SQL source

(0) Share

Report

Posted on by CU02061629-0

I have two tables in SQL Server: SourceTable and WritebackTable. This data displays in PowerBI. I want users to be able to select a row in the SourceTable and then enter data about that row in a new entry in WritebackTable. In order to do this, I need to pass information about the source row to the writeback table. How do I do this?

Example:

UserA is viewing the PBI report for ItemA. UserA wants to enter information that shows that ItemA has been reviewed for compliance. UserA will select ItemA and then click a drillthrough or a button and be taken to the tab that shows the form. The form will populate ItemA's ID and common name. UserA will then enter information into the form and save it to the writeback table. The writeback table is populated with information from the Sourcetable (ItemA's ID and common name, or even just the ID, that part doesn't matter), and the information that UserA entered about the item.

How do I make this work? I have seen information on making this work but none of those solutions work with SQL Server. Thank you for any help you can provide.

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

SyedBilal 42 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey! The trick here is the Power Apps visual inside Power BI — that's the bridge between your selected row and the writeback table.

The pattern in 4 steps:

1. Add the Power Apps visual to your report. In Power BI Desktop, drop in the Power Apps visual (built-in).

2. Drag the SourceTable fields you need into it. ItemID and CommonName at minimum. These become the "context" Power BI sends to your app.

3. Create (or pick) the Power Apps form. Connect it to your SQL writeback table. Inside the app, the selected row's data is available as:

First(PowerBIIntegration.Data).ItemID
First(PowerBIIntegration.Data).CommonName

Set your form's "ItemID" and "CommonName" labels to those — they'll auto-populate from whatever row the user clicked.

4. Save with Patch(). Your save button writes everything (the passed-in ID + the user's input) to the writeback table:

Patch(WritebackTable,
Defaults(WritebackTable),
{
ItemID: First(PowerBIIntegration.Data).ItemID,
CommonName: First(PowerBIIntegration.Data).CommonName,
ReviewNotes: txtNotes.Text,
ReviewedBy: User().Email,
ReviewedOn: Now()
}
)

Two things that catch people out:

After saving, call PowerBIIntegration.Refresh() so the report reloads the writeback data.

The user runs the app under their own identity, so make sure they have insert permission on the SQL writeback table.

No drillthrough page needed — the Power Apps visual lives on the same report tab, and as the user clicks rows, the data context updates automatically.

Was this reply helpful? Yes No
Suggested answer

11manish 3,160 on at

Like
a
(0)

Report
Copy link

Link copied!
For your use case:

Power BI + Embedded Power Apps + SQL Server is the simplest and most maintainable solution.

User selects a row in Power BI.

Power Apps automatically receives the selected ItemID and CommonName.

User enters compliance details.

Power Apps writes a new record to WritebackTable.

Power BI refreshes and displays the updated information.

This pattern works very well with SQL Server and is widely used for compliance tracking, approvals, comments, forecasting adjustments, and other writeback scenarios.

Was this reply helpful? Yes No
CU02061629-0 6 on at

Like
a
(1)

Report
Copy link

Link copied!

@SyedBilal Thank you for those steps! What has worked so far from your input:

1) Instead of bringing in both of the matching fields from the sourcetable and writeback table, I only bring in the one from the sourcetable. This temporarily worked and allowed me to writeback the row.

However, it did that while the form itself didn't show any data and only showed the Save button. The form itself says "Getting your data" and will not change to anything else. Earlier on in my testing, I was able to have it successfully display the form, but it has not worked in several days, ever since I tried tying the source table into the writeback data.

Too, the page that displays the data is too cluttered to add a PowerApps form to it. For the sake of testing, I created a table that had only the fields I am trying to pass and then created the form. This gave me the most success; however, I still can't actually see the form.

Third, when you write "After saving, call PowerBIIntegration.Refresh() so the report reloads the writeback data.", Where do I call this?

Fourth, I apologize for creating another top level comment to reply to you, but I am new to using this platform and do not see where a Reply button is available.

Was this reply helpful? Yes No
Suggested answer

SyedBilal 42 on at

Like
a
(0)

Report
Copy link

Link copied!

@CU02061629-0 No worries about the reply structure — many people hit that on this platform. Glad it partially worked! Let's clear up the remaining issues.

The "Getting your data" issue:

That message usually means the form is stuck waiting for data that isn't coming through. Set the form up explicitly for a new entry:

• Form DataSource = WritebackTable
• Form DefaultMode = FormMode.New
• Form Item = Defaults(WritebackTable)

Then in each data card, set the Default property to pull from the Power BI visual:

• ItemID card Default = First(PowerBIIntegration.Data).ItemID
• CommonName card Default = First(PowerBIIntegration.Data).CommonName

That will get the form rendering immediately and pre-populating with the row the user selected.

Where to call PowerBIIntegration.Refresh():

It goes in the OnSelect of your Save button, right after the Patch. Your Save button OnSelect should look like this:

Patch(WritebackTable, Defaults(WritebackTable), { ItemID: First(PowerBIIntegration.Data).ItemID, CommonName: First(PowerBIIntegration.Data).CommonName, ReviewNotes: txtNotes.Text, ReviewedBy: User().Email, ReviewedOn: Now() });
PowerBIIntegration.Refresh()

The semicolon chains them so Power BI refreshes right after the save.

The cluttered page issue:

You don't need the form on the same page as your main report. The cleanest setup is:

• Create a new page in the report and call it Writeback or Review
• Put only the Power Apps visual on that page
• Right-click your SourceTable visual on the main page and configure a Drillthrough that takes users to the Writeback page filtered by the row they clicked

This keeps your main report tidy and gives the form room to render properly.

Quick sanity check: if the form still says "Getting your data" after those changes, edit the Power Apps visual, sign in, and check the Data panel on the Studio side. A stale or broken connection to WritebackTable often produces that exact symptom.

Try those and let me know what you see — happy to keep iterating.

Was this reply helpful? Yes No
CU02061629-0 6 on at

Like
a
(0)

Report
Copy link

Link copied!

@SyedBilal Thank you! I did what you said and it very nearly worked. One of the 3 fields that I'm passing through did not pass through, and in fact it only worked to auto-fill in the other 2 fields if that 3rd field was null -- when the 3rd field was not null, none of the 3 fields auto-populated. Also, I'm not sure what the expected behavior of the refresh call is, but it did not clear the form fields of the previous nor did it allow me to select another one. It did, however, work to drill through again to a different (null in 3rd field) row from the initial page. Is the refresh just for the direct query?
However, since many of those 3rd fields were null in the data source, I decided to use a different field from the sourcetable to display on the form, Field 4. Field 4 is a calculation in the PBI and is not in SQL (if this is the issue, I can work with the folks responsible for the SQL sourcetable to get it added).
I ran into the following issues:
1) Is there any way to modify an existing form with new fields to be passed through? I tried to add Field 4 to the PowerApps visual in PBI but it did not seem to pass through. Is there any way to make it work? It would be annoying if every time I wanted to make a change like that to a form that I'd have to completely recreate the form.
2) Having recreated the form, however, none of the pass through fields are populating. This might be user error and I will try again tomorrow. But the defaults aren't coming through.
So, are there Best Practices for this kind of thing, in term of field types? The new field maxes at 244 characters but is that too long to pass? I assume the issue cannot be uniqueness because that's not in play with other field that is passing correctly. Is there something that has to be set up in the relationship between the tables in the model?
I've also noticed differences in when it writes a NULL to the SQL table vs. when it writes an empty string. With #2, it's writing nulls, so I take that to mean it's not getting the field at all from the PowerBIIntegration, rather than it reading the form input box as an empty string.

Was this reply helpful? Yes No