Power BI Writeback to Dataverse

Introduction
To start with, the Writeback Grid works best when combining it with the "DirectQuery" connection mode. This allows you to write back data in real-time and instantly see the changes reflected in all the other visuals used across a report. Follow this link to learn more about using the Dataverse connector supporting this DirectQuery mode.

Prerequisites

• Power BI license
• Dataverse Web API endpoint
• Azure AD App Registration
• Writeback Grid Custom Visual

Connecting Power BI to Dataverse
The first thing you might want to try is to test the above-mentioned connector to retrieve live data from Dataverse. This just requires you to login into Power BI and the rest will be figured out automatically for you based on the user's access rights: 

 

At this point, it's important to mention that the connector treats some "special column types" differently from others such as choices or lookups by already joining fields (labels) from other entities/tables. This is very useful for analysis and reporting but it's currently not supported to write back data across different tables/entities. In any case, the custom visual will let you know if you have populated a field that is actually not part of the configured entity/table.

Finding Dataverse Web API registering Azure App
The next step towards using the Writeback Grid in conjunction with Dataverse is to create and configure an Azure App registration for authentication by following this blog post. It provides a good walk-through and also outlines where to find the Dataverse Web API endpoint URL which is required for configuring the Writeback Grid Custom Visual further down. Here is another good resource for finding this endpoint, but basically you just need to login into your Power Apps account, choose the desired environment and click on Settings -> Developer resources.



Configuring Writeback Grid Custom Visual
To finally configure the Writeback Grid for Dataverse, you simply need to navigate to the Format pane within your Power BI report and you will find the following fields to be populated under the "Config" section:

• Backend type: Choose "Dataverse"
• Web API endpoint: Paste the before retrieved endpoint URL from Power Apps, the ending backslash will not make any difference
• Table/Entity: Type in the table/entity you want to write data back to. This is case-sensitive and the fields used in the visual must come from the same table/entity
• Azure Tenant ID/Client ID/Client Secret: This information comes from your previously configured Azure App registration which is linked to an (application) user finally making the commits 

FAQ:

Why do I need to provide the unique identifier (GUID) column in the visual?
The column is necessary for updating and deleting data to uniquely identify rows and it needs to be provided in its standard format, learn more here. If you don't want to have it visible though, you can simply hide it within the visual.

Why do I keep getting the message that the entity's unique identifier (GUID) needs to be provided?
By default, all tables have a single GUID unique identifier column named like the table/entity you provide plus "id", eg. accountid. Make sure that the column exists in the grid and that you haven't renamed it in your data model.

Why do I keep getting the message "Resource not found for the segment - Custom Entity"?
In most cases, this is related to custom entities created with a plural name. Please make sure to create custom entities with single names as the manually provided plural name in Dataverse is not considered by the writeback service. It just automatically generates the plural form of the table/entity name you provide which might not work if this is already provided in plural. 

Why do I get conversion problems when submitting changes using date or time fields?
This can be due to individual configurations or properties of the Dataverse field, some date and time columns are by default set to "DateOnly" which in some cases cannot even be changed. This requires you to define a custom format within the Writeback Grid without the time attached such as yyyy-MM-dd, learn more here. 

Does the Writeback Grid trigger Workflow actions when I write data back?
Yes, the workflow behavior is the same as if you work within your Dataverse environments or use the Web API, learn more here.

Does the Writeback Grid trigger Cascade actions when I write back data?
Yes, the cascading behavior is the same as if you work within your Dataverse environments or use the Web API, learn more here. 


If you face any other issues, please let us know service@creativedata.io