In this insightful video, Manuel Quintana from Pragmatic Works explores the integration challenges and opportunities between Microsoft Fabric and Power Apps. The primary focus is identifying roadblocks when attempting data write-back within Power Apps and how Fabric’s different storage options can impact success.
Read-Only vs. Write-Back Scenarios
Manuel begins by distinguishing between two core use cases for Power Apps:
- Read-only scenarios: Virtually all Fabric storage options work well.
- Read-and-write scenarios: These are more limited and present integration challenges.
Storage Options in Microsoft Fabric
Microsoft Fabric currently offers three storage solutions, each with varying support for Power Apps functionality:
- Lakehouse
- Supports read-only access via SQL endpoints.
- Does not allow
CREATE or ALTER SQL statements.
- Not suitable for write-back scenarios in Power Apps.
- Warehouse
- Allows
CREATE and ALTER statements.
- Primary key definition must include
NOT ENFORCED and NONCLUSTERED keywords.
- Still does not enable write-back from Power Apps, despite schema support.
- Fabric SQL Database
- Currently the only storage solution that supports full read/write capabilities.
- Enables standard primary key creation without special keywords.
- Best suited for Power Apps requiring data modification features like
PATCH or UPDATE.
Connecting Power Apps to Fabric
Manuel outlines two main integration methods:
- Using Virtual Tables
- Supports connecting to lakehouses and warehouses.
- Provides read-only functionality.
- Quick to set up via workspace and table selection in Power Apps.
- Using SQL Connectors
- Connects to SQL endpoints for all Fabric options.
- Only the Fabric SQL Database (not its SQL endpoint) enables write-back.
- Be cautious: connecting via SQL endpoint will still result in read-only access.
Write-Back Demonstration with SQL Database
To prove the write-back functionality, Manuel demonstrates the following:
- Connects to a Fabric SQL Database using correct server and DB name.
- Creates a Power App using the external SQL data source.
- Updates a record successfully (e.g., changing “Leia” to “Manuel”).
- Verifies the change inside the SQL database within Fabric.
This confirms that Fabric SQL Databases are currently the only option that supports write-back operations.
Tips and Reminders
- SQL endpoints tied to lakehouses and warehouses are read-only in Power Apps.
- Be careful with overwriting tables—it may remove key constraints necessary for integrations.
- Always use the SQL database object’s connection info, not the SQL endpoint, when write-back is needed.
- Power Apps may eventually support write-back in warehouses—stay updated!
Conclusion
Manuel wraps up the video by encouraging users to explore the powerful combinations of Microsoft Fabric and Power Platform. While write-back capabilities are currently limited to Fabric SQL Databases, there’s ample flexibility for read-only integrations using virtual tables and SQL endpoints.
If you’re exploring the Power Platform for your business and want to build apps that modify data within Fabric, the Fabric SQL Database is your go-to solution.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Fabric and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.