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
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.
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.
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.