Use dynamic JSON content to update Dataverse Table from Excel Sheet (uploaded)

[Herr_Mike]

Hi all,

I have built a flow that allows a user to upload an excel sheet to sharepoint - from where the columns are renamed to a standard format (the datasource of the excel sheet has ":" and "." in the column names - fun times). The excel sheets are essentially mark sheets from different modules, and each sheet will be expected to have a different number of columns etc. I have so far been successful in retrieving the data from the excel sheet and patching it through to the dataverse table using the "Update a Row" Dataverse action in an "Apply to each" on the imported excel sheet (excel is uploaded to sharepoint where a script is run to create a table based on the used range).

The flow runs fine when I type the JSON in manually in the Update A Row action, but when trying to use a JSON schema which is retrieved from a different dataverse table (as the module changes different schemas will be needed, so a table stores all this module specific information which can be recalled using variables in the flow) the Update a Row action fails.

Now, this would action be expected - as it relies on using expressions/formulas that are stored in a table's cell - which would essentially mean anyone wise enough would be able to run custom functions if they add it in a cell. Is there any way it would be possible to do this?

Here is the parameters from the typed out JSON schema for the Update a Row action that works:

"parameters": {

            "entityName": "@variables('Table Logical Name')",

            "recordId": "@items('Apply_to_each_-_DV')?[variables('Mark Row GUID')]",

            "item": {

                "dv_name": "@{items('Apply_to_each_-_LH')?['First Name']}",

                "dv_surname": "@{items('Apply_to_each_-_LH')?['Surname']}",

                "dv_emailaddress": "@{items('Apply_to_each_-_LH')?['Email address']}",

                "dv_individualassignment": "@{if(isFloat(coalesce(items('Apply_to_each_-_LH')?['Col 1'], 'notFloat')), float(items('Apply_to_each_-_LH')?['Col 1']), float('0'))}",

                "dv_groupassignment": "@{if(isFloat(coalesce(items('Apply_to_each_-_LH')?['Col 2'], 'notFloat')), float(items('Apply_to_each_-_LH')?['Col 2']), float('0'))}"

            }

        }

And I am trying to do something like:

, where the JSON Schema variable is a string retried from a DV table.

The end goal is to patch the data from the excel to the DV, but this needs to be done using values that are stored in a DV table (in case it needs to be updated if a module adds another assessment or the entire structure of the module is different the following year...

I would appreciate any assistance! Thank you.

[Expiscornovus]

Hi @Herr_Mike,

Don't know if it helps, but you could also execute such kind of PATCH requests via the Dataverse Web API. That approach might give you a bit more flexibility since these kind of request can be executed via a HTTP action.

Below is a basic example from the Microsoft docs to PATCH a row:

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/web-api-basic-operations...

I have also a written a basic/note-to-self article a couple of years ago on how you could interact with this API within Power Automate:

https://www.expiscornovus.com/2021/03/14/how-to-use-the-dataverse-web-api/

[Herr_Mike]

Thank you for the suggestion! I will give it a try this week and (hopefully) come back to accept as solution. Looks promising.