Read Excel File from SharePoint Online and create Records in Dataverse | Power Automate Flow

November 13, 2023 priyeshwagh777 Dataverse, Microsoft Flows, Power Automate dataverse connector, excel online power automate, list rows present in a table, sharepoint online

One of the most common scenarios is to be able to pick an Excel spreadsheet from a SharePoint Document location and create records in Dataverse.

There are several ways to do this. But, one of the most common scenarios could be to use Power Automate Flow and use Excel Online and SharePoint Online connectors to perform this operation!

Scenario

Here’s the scenario which you can expand on and fit the same according to your Use Case –

There’s a file in a SharePoint Document location called as AccountImport.
This file has some Account information that needs to be inserted in Dataverse
Here’s the Excel content which has a Table in it.
And this data needs to be Inserted in Dataverse [You can either Automate this Flow, Make it On Demand — based on whatever is suitable for you]

Let’s create an On-Demand Flow in order to pick this File and then insert into Dataverse.

Power Automate Flow

Here’s the Power Automate Flow which we’ll create. For the same of this example and to keep it simple, we’ll create an On-Demand Flow –

Create an On-Demand Flow in Power Automate [You could even choose to run the Flow when a SharePoint file is created or changed or even to Run the Flow once every day — depending on what best suits your case]
Then, look for the action called as List rows present in a table from Excel Online connector
In this Action, you can select what SharePoint Site is to be selected where you have the File in the Document Location.
Also, then select the Document Library where the Documents reside. That’s the ideal place where you would want to place your Documents.
Finally, select the File itself by navigating from the Folder icon on the File property as shown below.

And then select the File once you find it.
Once you have selected the File, the Table itself will be available to pick up from the Table property. Make sure to convert the Excel data into a Table.
Now, once all the Properties are set on the List rows present in a table action, select a For Each loop in the Flow.
And in the Inputs, give value (List of Items) from the List rows present in a table action which we just completed above.
Once this is set, select Add a new row action from the Dataverse connector in order to create the records sequentially in Dataverse.
Here, map the Columns to the fields in Dataverse. Firstly, select the Table in Dataverse you want to insert these records into.
Then, select the fields from the Excel which the Excel connector itself separated out for you.
Once you complete all the Fields from the Excel to the Dataverse connector.
Save and Test the connector itself.
This will create the records in Dataverse (Dynamics CRM)

And it’ll Run in a few moments and succeed if everything goes right.

Dataverse Records

Now, let’s see the Flow in action –

Because this is an on-demand Flow, you can Run it whenever you want. And when it Runs successfully, records in the Dataverse will be created as shown below

Hope this helps!

Here are some Power Automate posts you want to check out –

Thank you!

Migrate Flow to latest Microsoft Dataverse connector

March 31, 2023 priyeshwagh777 Power Automate dataverse connector, migrate flow, migrate to the new dataverse connector, power automate dataverse connector

If you are using the legacy Dataverse connector which has the gray logo and looks like the below.

You can run Flow checker to identify if Power Automate can help you migrate the Flow to utilize the latest connector.

Flow checker message

Here’s what the old Flow looks like –

You would see the below message once Flow Checker suggests you changes –
Once you click on the “Open the migration assistant” link on the suggestion, it’ll pop-up a window to ask if you are ready to allow Migrating the Flow to the latest Dataverse connector.
Once you click on Migrate, it’ll start the migration process and based on how lengthy your flow is – in a few moments your new Flow will be ready.
So, once the migration is completed, you’ll see a message like this.
Once you click on Open the new flow, you’ll notice that the new Flow now has (Migrated) written in.
And once you click on Edit, you’ll see that the Flow step where old Dataverse connector was used has been replaced by the new Connector

Hope this helps!

Here are some Power Automate posts you want to check out –

Primary Key of Activity type entity in a Dataverse connector in Power Automate | Quick Tip

July 26, 2021 priyeshwagh777 Power Automate activity entity, cloud flows, dataverse connector, Power Automate, update a row

In case it’s your first time creating a Cloud Flow using Dataverse connector and dealing with Primary Key of the Activity Type of entity, here’s a quick tip to keep in mind.

Normal Entity

As a creator for Flow, let’s say you have listed Records of an entity called as Accounts (out-of-the-box), you’ll notice that the name of the entity

Activity Entity

Now, if you try to search for the Name of the Activity entity, it won’t appear as it does for other entities, instead just search “Unique” and you’ll find an entry for Unique identifier of the activity entity –

Name of my Activity entity is ‘Snapshot’, but it doesn’t show up when I search Snapshot.
So instead, just search “Unique” or “Identifier”
On the contrary, out-of-the-box entities have their own names named as Primary Keys

Here are some more Power Automate / Cloud Flow blogs you might want to check –

Search Rows (preview) Action in Dataverse connector in a Flow | Power Automate

May 24, 2021 priyeshwagh777 Power Automate cds, common data service, Dataverse, dataverse connector, Dynamics 365, Power Automate, search rows

As Dataverse connector keeps getting updated from time to time, here’s a new Search rows (preview) Action which you must be seeing in the Dataverse connector in Power Automate. Let’s see how we can use this Action.

As it suggests, that this is still in preview! So kindly take a note of that.

Search Rows (Preview)

Search rows is an Action in the Dataverse connector and you’ll be able to see it like this

Enable Relevance Search

Let’s assume you went ahead and used this connector in your Flow without having Relevance Search enabled in your D365 CE organization, you’ll see the Search Rows throw the below error.

And the error is described as –
To make Search Rows work, Relevance Search must be enabled for you Dynamics 365 CE / CRM environment. Head over to the System Settings in Dynamics 365 under Settings > Administration > System Settings. And in General tab, look for Relevance Search option, check-mark it and save.
Here’s the full Microsoft Documentation on how to use queries for Relevance Search – https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/relevance-search?WT.mc_id=DX-MVP-5003911

Using Search Rows action

Let’s see how this Action from the Dataverse connector will work –

Now, once you have selected the Search rows action, below are the features which I’ll explain one by one –
Let’s first look at the last item i.e. Return row count. As it says, will result the count of results returned if set to Yes.
Will return -1 if set to No.
For Search Type and Search Mode to be covered, these need to be explained extensively since it covers several factors. To keep it short for now, I’ve linked the Search Type and Search Mode documentation as below
https://docs.microsoft.com/en-us/power-automate/dataverse/search#search-type?WT.mc_id=DX-MVP-5003911

And the short Summary of the same is:
Search Type: “simple | full” There are 2 types called as simple and full. Default = simple. Both have different functions within them that you can use on the Search Term.

Search Mode: “any | all” By Default – any. This defines if any criteria of the Search Term is to be considered or all must be true based on different syntax and operators used.
Row Count denotes how many records should the results Return which is standard across other Dataverse actionss.
Also, Row Filter uses OData style filtering which we’ll omit in this example to keep it simple. 😊
Now, let’s enter a Search Term and keep it simple, I’ll pick an example: “Contoso“.
Next, look at the Table filter – If I leave it blank it’ll search for Contoso across all Tables in Dataverse.

Result to show from all Tables as per the above Search rows term –

Now, I’ll add account and contact (I do have some records in the Opportunity as well which has the keyword ‘Contoso’. So those will be filtered out)

And the result will be as follows –

And if we look at the Raw Outputs to see how data is retrieved, we get the below –
Now, let’s look at Sorting.
Now, in the Sort by filter, I’ll enter the field name ‘name‘ and desc as the order of the Sort.
And then, we’ll check the results

And the results were as below –
Next, let’s look at Facet Query. basically, it drills down on the Results which are already returned as a part of the main query and serve as metadata for the same to gather similar information together.
Here’s how I enter a Facet query – Ex: contact.address1_city. Meaning, it’ll return Address 1: City from all the returned Data and store it under Facet Query.

Now, when we run this, we’ll get the following results –
Let’s look at what Skip Rows does.
It’ll only omit the records from the already returned Results. Example: Even if the Result returned 5 records, it’ll Omit/Skip first x records. But still show that the Results returned are whatever the Query is supposed to return.

In the below example, out of 5, I’ll skip 4 rows.

And when you run the Query, you’ll find that the Record Count is 5, but only 1 entry was available in the Output.

Which Columns are Searchable?

There are 2 points to keep in mind to know and configure which all columns will the Search be performed on.

If you are aware of how you can configure the Quick Find views in Dynamics 365 CE, same is applicable here since this works off of Relevance Search itself.
In any Table/Entity’s Quick Find view, make sure the columns are selected in the Add Find Columns
The ones selected in Find Columns are the ones on which the Search will be performed.

Here’s a YouTube video I made to demonstrate the same –

Hope this was useful!

Here are some more Power Automate / Cloud Flow posts you might want to check –

Thank you!

Invalid XML issue in Dataverse connector for List Rows action | Fetch XML Query | Power Automate

May 4, 2021 priyeshwagh777 Power Automate cloud flows, dataverse connector, dataverse flow, encodeuricomponent, fetch xml, fetchxml, invalid xml, Power Automate, special characters

While using Dataverse connector [formerly known as Common Data Service (Current Environment) connector] for different operations, List Rows is one of the common Actions we use in Flow implementations with Dataverse.

If using FetchXML Query as filter is your choice, there one of the most common issues with an XML is having an Invalid XML due to the special characters in the data we are passing.
Many times, this issue goes unidentified since we end up checking only syntax!

Here’s what you can also add as an additional check to escape the special character scenario in FetchXML query in a List Rows action.

Invalid XML Issue – Special Character

Let’s look at one of the common examples where you’ll see this error on runtime when a FetchXML issue appears

You have a Flow wherein you are using FetchXML query to list records in Dataverse connector using List Records action.
But when happens is, when this Flow Runs, it results in an error on the List Records step.
And if you look at the right hand pane for the error, it could be a Invalid XML error which you would get in Dataverse (or while working in Dynamics CRM if you have experienced in the past).
So, let’s look at what could be wrong in this case among several other reason of using FetchXML and getting syntactical errors.
If you look at my XML below, it looks like there could be some parameters that have special characters in them and hence, your XML might fail if not anything else.

Let’s look at how we can resolve the same.

encodeURIComponent() function

As a solution to this, you can use the encodeURIComponent function in Flow to counter this problem –

Now, in ideal cases, you parameters could be coming in from elsewhere which you would add in the FetchXML as parameters.
Let me store the same in a sample String Variable for now.

In the Value, I’ll look for an expression called as encodeUriComponent()
Then, I simply enter the value which I’ll pass on to the Fetch XML filter to be used later on. And then I enter the value in the function.

And it looks like this in your variable.
Now, you just pass this on to the Fetch XML itself.
And now, this should work successfully