Consuming Excel Files with Cloud Flows for the Dataverse

Do you leverage data from multiple applications or services? If you haven’t already found a way to integrate the data between them, you should check out Microsoft Cloud Flows, which are part of Power Automate and part of the overall Power Platform.

What are Cloud Flows?

Cloud Flows allow power users to build workflows that integrate a diverse set of apps and services, including Dynamics 365, SharePoint, SQL Server, Act!, Twitter and many more! Each workflow can have one trigger, such as a timer or an incoming email or tweet, and one or many actions, such as creating a Lead in your application of choice and sending a text message to a user. Most of it requires little to no code, but a Developer can help extend the functionality.

Consuming Excel files with Cloud Flows

Within the Power Platform, there are many pre-built tools that you can leverage to consume Excel files. One method is to utilize the pre-built Excel connector which can parse an Excel file uploaded to SharePoint or One Drive.

Transforming Excel data for the Dataverse

Since there is a pre-built Excel connector, parsing Excel data and transforming it for the Dataverse may seem straightforward to do (and it is for the most part!), but here are a few tips to make the transformation smoother:

• Retrieving the ‘table’ within the excel document

  • One action within the Excel connector is to retrieve the table within the excel document. Part of the challenge with this is that it wants you to specify the table name, which could be different for each excel file, especially if they were generated differently. One workaround for this is to retrieve all tables in the excel file and add an “apply to each” condition. This way avoids having to hard code a table name within the action. Validation of the columns can be performed later on after parsing the data.
    
    

• Formatting the Excel cell value for the target Dataverse field

  • Since the Dataverse target field may be of a different type than the excel cell value, you’ll want to check that the value is the appropriate type before inserting into the Dataverse. Some examples include:

    • Date values:

      • When read from excel in Flow, these values will come over as a number such as the following: 44868. If your target Dataverse field is of type datetime, you’ll want to transform this in flow to avoid an error when inserting. You can use a flow expression such as the following:
        addhours(addDays(‘1899-12-30’,int(items(‘Apply_to_each_row’)?[‘Posting Date’]),’yyyy-MM-dd’),4)

    • Decimal/Currency values:

      • When read from excel in Flow, these values will typically come over as a string and may not have been validated in the Excel document (depending on how it was designed). If your target Dataverse field is of type decimal/currency, you’ll want to validate it’s in the format you need using a flow expression such as the following:
        if(isFloat(items(‘Apply_to_each_row’)?[‘Credit Amount’]),items(‘Apply_to_each_row’)?[‘Credit Amount’] , 0)

• Linking the Dataverse record to an existing Dataverse record

  • Since the Dataverse is a relational database, you’ll likely need to link your record to another on create. To link a record, you need to refer to the EntitySetName with the guid of the record wrapped around parantheses.  If you’re not sure of the EntitySetName, you can add the related entity to your solution, export it and search for the <EntitySetName>myEntity</EntitySetName> tags. Doing it this way will help ensure that you have the correct spelling and will help you avoid cryptic errors while testing.

Once set up, the flow can be shared with power users within your organization who can then build additional cloud flows to consume and transform your excel files for specific needs.

Contact Beringer Today!

We love to implement Microsoft Dynamics 365 and Power Platform solutions here at Beringer.  We’ve been working with Microsoft Dynamics since its inception, and we’re always finding innovative ways to implement the latest tools and help automate business processes.

Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in Microsoft Dynamics 365 and CRM for Distribution also provides expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing, Email Security Implementation and Training, Unified Communication Solutions, and Cybersecurity Risk Assessment.