Best way to compare Excel data against Dataverse data?

(0) Share

Report

Posted on by shogg

I am looking for the best way to compare data from an excel sheet to data that is used in our Dynamics Sales application.

Use case: When we attend trade shows, we usually receive the list of attendees in advance via an Excel spreadsheet. We can only use the list one time, so we carefully plan the communication that goes out to the attendees. We like to tailor the messaging based on whether the attendee is a current client of ours, a current prospect, or a partner / vendor / competitor. I would like an automated way to see if the people on the list are currently in our CRM as contacts, customers, leads, etc. Ideally I'd like a way to do this that doesn't require me to import the list into Dynamics, since we aren't permitted to use it more than once. It would be great if I could just add another column to the Excel spreadsheet that indicates whether the attendee is in our CRM in some capacity and have a flow that runs and populates the sheet.

I tried a few different flows in Power Automate but was not successful. I am a novice, though, so rather than detail the flows I tried, I'd rather see how others might approach this problem. Thank you in advance for any suggestions you're able to provide!

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Abdul Wahab 12,119 Moderator on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi shogg,

First, you need to decide the criteria by which you can define the duplication. For example if the 'name' + 'contact number' is in the excel or in the CRM than it is a dupliatioon. After that you can create a key in that table and use excel import to create new records.

Was this reply helpful? Yes No
shogg 39 on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi Abdul,

The key would be the email address, which would be unique. However, I do not wish to import new records into the CRM. I only want a way to check which email addresses in the Excel sheet already exist within my CRM.

Was this reply helpful? Yes No
Suggested answer

Abdul Wahab 12,119 Moderator on at

Like (0)

Report
RE: Best way to compare Excel data against Dataverse data?

Hi shogg,

In that case, Power Automate is a suggestion for you.

Make two folders in excel

In progress

successfully parsed

Use the below component if you are using share point for that purpose

Parse all the rows of excel using the below component

Use the below Components to parse each record

When the record is found in the CRM then update the excel sheet using the below components

Once the file is updated then move it to the folder successfully parsed using the blow component

Was this reply helpful? Yes No
Verified answer

Leah Ju Microsoft Employee on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi shogg,

You can create one flow to achieve your goal, and you need use 'Excel Online (Business)' connector to get and update data in the excel.

https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/

Steps:

Create EXCEL with an account that has access to CRM data.

Select all data to create table in the excel:

Go to Power Automate to create one instant flow.

Initialize variable

List rows present in a table(Excel):

List rows(Dataverse):

Get and set record count for the list:

Condition:

--under Yes branch, 'Update a row(Excel)' to update Flag column:

Save and Test:

Was this reply helpful? Yes No
Leah Ju Microsoft Employee on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi shogg,

Have any updates?

Please feel free to contact me if you have any questions!

Was this reply helpful? Yes No
shogg 39 on at

Like (0)

Report
RE: Best way to compare Excel data against Dataverse data?

Hi Leah,

Thanks very much for this suggestion, I appreciate the detail! I tried a variation of this yesterday and had mixed success.

I had the flow update the spreadsheet in both the If Yes and If No branches of the condition, so when the flow succeeded I expected the "Flag" column to say either Yes or No. Initially I could see it was only processing 256 rows of the Excel sheet until I realized I needed to adjust a setting on the Excel step to allow for pagination. I set the threshold to 1000 since there are 777 rows in my spreadsheet. For whatever reason the default is 256 rows. (See https://www.c-sharpcorner.com/article/retrieve-more-than-default-number-of-rows-of-data-from-excel-using-power-automat/)

The good news is the flow succeeded (after 22 minutes). When I look at the Excel, I do see both Yes and No values present and they seem to be accurate at first glance, which tells me my filters are probably good. However, more than half of the rows are blank. Only 348 rows have values, and it's scattered throughout (not the first 348 rows, for example). I can't tell why this might be. Since the flow succeeded, I don't think it timed out, and I've tried it multiple times with the same result.

I confirmed:

The range of the table I'm calling looks correct

The List Rows output appears to be working; the "Apply to each" step shows 777 rows:

Any ideas?

Was this reply helpful? Yes No
Suggested answer

Leah Ju Microsoft Employee on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi shogg,

Based on your screenshot, you used two 'Apply to each' actions:

How many lines are shown in the "Apply to each 2" step?

Can you check if the subsequent steps are placed in the 'Apply to each 2' step? They should be at the same level as 'List customers'.

My flow:

There is no need to use 'apply to each 2' action:

--we just need to get the number of records returned from the "List rows" step and don't need to do anything more with it.

Can you share screenshots of all your steps?

Was this reply helpful? Yes No
shogg 39 on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi Leah,

Thanks for your feedback! The second "Apply to Each" step is in there because my condition includes additional criteria. I'll explain in my screenshot.

Some background first: I chose to adapt your instructions for something slightly different -- this flow is checking for Account records (we renamed the Accounts table to Customers) that already exist in our CRM, matching on "Company Name" in the spreadsheet. This posed two challenges. First, the Company name in the spreadsheet is sometimes a full name (example: Alpha Bet Company) and sometimes an acronym (ABC). For our customer records, we do capture both the full name and the acronym in the table, but these are two different fields. Second, I only want it to compare against customers where Relationship Type = Customer (customertypecode = 3). So, here is what I ended up with.

First three steps are the same:

Next, the Apply to Each step:

Note that the filter step has an OR statement ensure both the Name and Acronym fields are being checked for a match on the Company column in the spreadsheet.

length(outputs('List_Customers_that_appear_on_the_Excel_sheet')?['body/value'])

The next set of steps is contained within the second "Apply to Each" step. This is because I included a second condition here to account for including only CRM Customer records where the relationship type = 3 (Customer, instead of Prospect, Vendor, etc). Because the condition is referring to Dynamic Content from the "List Customer" step, the second Apply to Each step is forced. Originally I had applied this filter on the "List Customers that appear in Excel spreadsheet" step but that wasn't working. After the flow would run, not only were there still rows with blank values, but the rows WITH values were not accurate. Either the OData filter did not like both and AND and an OR condition or my syntax / order was wrong. In any case, when I made this change and moved the filter to the condition, this fixed the accuracy but did not fix the fact that rows were left blank.

For the last time the flow ran, the "Apply to Each 2" step shows this. It says "1 of 1", but I think that is because it is contained within the first "Apply to Each" step. As I toggle through from 1 of 777 to 2 of 777, etc, the "Apply to Each 2" step continues to say Show 1 of 1.

Looking forward to hearing your thoughts on this. Thank you for your help!

Was this reply helpful? Yes No
Suggested answer

Leah Ju Microsoft Employee on at

Like (0)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi shogg,

The reason is that you set the condition inside the ''Apply to each 2'' action, only records that meet the 'List Customers' criteria can be run inside.

--Out of a total of 777 rows, 348 could be returned from the 'List customers' action and then those rows would be updated to 'Y' or 'N', but the rest would be ignored.

You should change it like this:

--Separating the two conditions, Condition 1 must be outside the 'Apply to each 2'action

Was this reply helpful? Yes No
shogg 39 on at

Like (1)

Report

RE: Best way to compare Excel data against Dataverse data?

Hi Leah,

I am happy to report that this worked perfectly after I made this adjustment. The data is complete and accurate. Thank you very much for your help!

Was this reply helpful? Yes No