PBI Report/Power APP cannot add or update data

[Babette]

Please, need some help! I am trying to create a PBI report using PBI Desktop (Jan 2021 version), publish the report to PBI Service, open to edit the report in PBI Service and then embed Power APP to perform write back to Azure SQL DB or OnPrem Excel.

Scenario 1: Write Back to Azure SQL DB
In PBI Desktop...
1) Create PBI report with Data Source = Azure SQL DB table 'StoreDim' using IMPORT data connectivity mode.
(I was looking at GuyInCube-Patrick's video, he was able to switch the Storage Mode to 'Dual'. But I don't have the option to do the same - not sure if this is reason why I cannot get the Write back successfully completed. Also I cannot find the Power APP visual to import into the report.)
2) Added a Table visual and pulled a couple of data items into the Table.
3) Saved and published the report.

In PBI Service...
1) Opened the report for Edit.
2) Added the Power APP visual into the report and pulled 1 data item 'Location' into PowerApps Data field.
3) Click on Create new - this opens the Create Power APP studio.

In Power APP Studio...
1) The Gallery displays the 'Location' info by default as it was what I included in the above step.
2) I added a EDIT FORM to the screen.
3) For Data source, I used the Connector: SQL Server to connect to the Azure SQL DB table 'StoreDim'.
4) I am able to see all the fields in the table and picked a couple to add to the EDIT FORM.
5) BUT, the Control type for all the fields is 'View text' ONLY so I cannot make it EDITable.

In SQL Management Studio, I can connect to the Azure SQL DB, create the table 'StoreDim' and insert rows into it.

Are there special permissions required in Azure SQL DB for applications to WRITE/UPDATE?

Scenario 2: Write Back to OnPrem Excel
Much the same as above except PBI Desktop report Data Source = OnPrem Excel file (Excel has the defined Table with a name 'Location').
In Power APP Studio, for Data source, I used the Connector: Import from Excel to connect to the OnPrem Excel file.
I can see the 'Location' table name but with no fields available for me to select.  Why can't I see the fields in the excel?

Our organization has just got the Azure Cloud configured for us to try some test cases. I have 'Contributor' access privilege.
I think i have got all the steps in the right order to test Write Back.
It looks like permission/configuration is blocking me. 

I VPN into our organization's network and use single sign on AD into Azure to work on the test cases.

Please let know if I am missing a step or some configuration we need to know etc.   Thank you in advance.

[GilbertQ · Accepted solution]

Hi @Babette when creating the Power App you must make sure that the table has got a Primary Key in order to insert data.

If you can check?

[lbendlin]

yes, that's the right client for that purpose.  If you need to work with Report Server then you need to continue to use the RS client.

[lbendlin]

"PBI report using PBI Desktop (Jan 2021 version)" - Did you mean Power BI Desktop for Report Server?  There is no Jan 2021 version for Power BI Desktop.

[Babette]

@lbendlin  Yes, I am using the client Power BI Desktop (Version: 2.88.1382.0 64-bit (January, 2021) for Report Server and I published my report to our Power BI Service.  In PBI Service, I embedded Power App to try to update/add data to a table in Azure SQL DB.

[lbendlin]

Can't do that. The clients are different for a reason.

[Babette]

@lbendlin  Could you advise please what do I need? I have to create a Power BI report and embed Power APP to test write back. Thx

[lbendlin]

Install the correct Power BI Desktop client for your scenario.  If you use Report Server (on premise) then use the RS client. If you use Power BI Service then install the regular client.

[Babette]

@lbendlinThe Microsoft download site only shows 1 option for Power BI Desktop install 

 https://www.microsoft.com/en-us/download/details.aspx?id=58494 .  We have a Report Server which i ignore as I want to create a Power BI report - publish to Service - and then embed Power App.  

Would this install https://www.microsoft.com/en-us/download/details.aspx?id=58494  be a regular client i need?  Thank you.

[GilbertQ]

Hi @Babette 

You should be able to follow the details in the link below from the Power Apps team on how to enable write back to the data.

Creating Updateable Power BI Reports with PowerApps by Ike Ellis | Microsoft Power Apps

There also is my Youtube video showing how to achieve this: Power BI Writeback with Power Apps by Gilbert Quevauvilliers - YouTube

[Babette]

@GilbertQ  Thank you so much for the links.   I will go over the steps (the Azure environment is setup by another group in the organization) hopefully i have enough pemissions.  Will send an update on how it goes 🙂

[Babette]

@GilbertQ  I have a chance to check with the DBA this morning. I was not a member of the db_datawriter role (i am dataowner). DBA added me to db_datawriter to the DW DB.  For a quick test, I used the 'Start from data > SQL Server ' wizard in Power App and source data from the DW DB (made the SQL connection earlier). Screen shows data from the table.  I should be able to see the '+' in the top right of Power App screen if I have 'Add/Update' permission to the table.  But I could only see data in VIEW mode.

Not sure what else i can check!  Any suggestion please?

[GilbertQ · Accepted solution]

Hi @Babette when creating the Power App you must make sure that the table has got a Primary Key in order to insert data.

If you can check?

[Babette · Accepted solution]

@GilbertQ Yes, after adding the Primary Key to the table, Insert/Edit works perfecly 💃. I followed your youtube - create the PowerApp first and then add it to my report.  This went so much smoother than my earlier tests to Make new app in the report and i was getting different kinds of errors.  Thank you so much for helping 😊.

[GilbertQ]

Hi @Babette 

That is awesome thanks for letting us know