Manage CDS Entity Data – Microsoft PowerApps Office Add-In

Microsoft PowerApps Office Add-In enables user to read, modify and insert data in CDS environment. Add-In is available for download here for free, or you will be prompted to install Add-In for first time when you open entity data in excel from PowerApps portal. Add-In establishes connection to PowerApps OData service.

Note: Add-In is compatible with Office 2016 or later versions only.

Using Microsoft PowerApps Office Add-In in excel you can create, update and delete rows. Let’s explore this Add-In.

• Login to PowerApps web portal. Click on Entities under Data tab on left side menu.
• Select any entity. In my case its “Company Products”.
• Click on Open in Excel option on top menu which will export selected entity’s data in excel. Excel will ask you to install Add-In if you are using it for first time or will load Add-In for you.

• Create Rows – To create new rows, click on New button which will add a new row in excel sheet. Enter values as required.

• Delete Rows – To delete existing rows, simply select row and delete like you delete from excel.
• Update Rows – To update data, change values as below:
  • If string field – type in updated value
  • If option set field – select field, you can type updated value or Add-In will load all option set values for you to select. Once you select, selected value will be updated in selected field.
• 
  • If lookup field – select field, again you can type updated value or Add-In will load all lookup records. Once you select, selected value will be updated

• Read-Only fields – Fields such as Created By, Modified By are read-only field and cannot be updated. As you will select any read-only field, Add-In will show that field in red as shown below.

• Publish Changes – Once done will all changes, you need to publish them using publish option to commit these changes to CDS. When you click on Publish button, it will prompt you for number of records being created, deleted or updated.

• Error Handling – In case you have modified a read-only field or updated duplicate value in Primary key field then publish changes will throw error
  • On Read-Only field modified

• Error occurred while publishing changes. For ex – Primary key violation. Error-ed row will be highlighted.

I find this Add-In very rich and useful where users do not have capability to manage data in CDS from PowerApps portal which is one of upcoming feature to be released.