using powerapp to write to a fabric warehouse

[smeetsh]

Hi all,

I am trying to come up with a way to create a CRUD editor, for end users, so they can easily update a table in a fabric warehouse.

I have been following the below video (I have not tried the powerautomate step yet)
https://www.youtube.com/watch?v=mVsW9kSjjJ4

The reason I made a side step is that this video is 6 montsh old and he uses the powerautmate flow, since back then fabric did not have primary keys, however since the last update it has.

So i decided to create a sql connection, which worked since i can display the data of the table in the app

But now i need to write back data .

As a simple test I create a dropdown box with 3 id's, made sure the ID column was primary key, had 1 input field that displays the current value (textinput1) , and a new input field (TextInput2) that will hold the new value.

My submit button I have the following code:

Patch(
xxx_fact_test_types_3,
LookUp(xxx_fact_test_types_3, id = Dropdown1.Selected.Value),
{known_test_type: TextInput2.Text}
);

No squigly red lines so yay fingers crossed. Upon submit  , alas no dice the error is: Network error when using patch fucntion: an error occured on the server...so no longer a message about a lack of primary key or a read only DB.

Copilot suggested checking to make sure the rights to write to the table are correct, but I am an admin for that workspace. My question is: Is it still simply not possible to use the sql endpoint, using the sql connector, to write to a warehouse or did I miss a security setting somewhere?
Some screens of the current app:


Cheers

Hans

[lbendlin · Accepted solution]

The SQL Endpoints in Fabric are read only.

[smeetsh · Accepted solution]

Hi All,

What has been lost in this thread over time is that the use case was to deliver a CRUD editor for end users to use, to update data in an existing warehouse table.  Unless something major has been updated, since the start of this thread, that is not possible, though if you were to have an on prem sql server that would not be an issue.

Updating a table using TSQL, stored procedures etc, yes that is pretty straightforward, but that is not an option for end users, and me writing custom code every week to update a table is not ver efficient.

We have taken a different approach, it works, even though it's not as pretty as a powerapp, is to have an excel sheet on a share, reachable by the users that need to have this fucntionality. Next we have a pipleine that ingests it in to lakehouse and from there into warehouse.

Using sheets like that has an inherent risk of end-users damaging the sheet, due to the access they have, which would make the pipeline fail, but overall it is a workaround.

Cheers

Hans 

[Ankitachelani]

Have you received any solution to this?

[smeetsh]

Hi @Ankitachelani  At this point in time there is no solution for it, unless you take the workaround approach with an excel sheet

[PeterDaniels]

Apologies for steering this thread in an uproductive direction, Hans. Was just trying to explore some options. I agree that we need a simpler way of editing smaller datasets/tables in Fabric DW like we do for other MS TSQL engines.

[HowD]

I've made this work by using stored procedures and power bi Intergartion for the power app. I havn't tested if it can be done without the power bi intergration. There is a video floating around somewhere which shows you how to acheive it. 

[HowD]

I just tested and you dont need to use power BI intergration. You just use the stored procedure like a patch

[PeterDaniels]

This asnwer is confusing to me. I am able to insert and update data against the warehouse endpoint.

[HowD]

Yes, so you can enter and change data in a power app and it updates your Fabric Datawarehouse. 
Here is the video I used, It worked for me. In the video it inludes having your app in a Power bi report and updating the data in a report, but you can ignore that part. Essentially you just to created a stored procedure in your warehouse, then use that stored procedure in the power app (usually in a 'onselect' of a button) 

https://www.youtube.com/watch?v=exJWifeysRs

[x_mfd1979_x]

I think the issue is the Patch functionality in Powerapps doesn't currently support writing to Fabric Warehouses - my hunch is it will be at some point down the road.  

[smeetsh]

That would certainly be great, another problem for now with PowerApps is though that you have to have primary, which is something Fabric only started to support somewhere last year, but it is not enforcable. I would love to have the flexibility in PowerApps to be able to choose my own key like a combination of a locationID and date,  to determine a unique record. But I digress 😄 

[lbendlin]

Using the SQL endpoint?

[PeterDaniels]

Yes. In SSMS. Not sure how the Auth works from power apps, but I do know you can CRUD data in a Fabric warehouse using TSQL. 

[lbendlin]

That would be a welcome if unannounced change. Back in September 2024 this was all strictly read only.

[PeterDaniels]

Warehouse and now SQL Database SQL endpoints are rw via TSQL. Lakehouse SQL endoint is not. It's read only. Have to use Spark SQL or pyspark for write ops

[lbendlin · Accepted solution]

The SQL Endpoints in Fabric are read only.