Unanswered

Compare 100 items in excel with 100000 items in Dataverse.

(0) Share

Report

Posted on by brk108

Hi All,

I want to compare 100 items in excel with 100000 records in dataverse and If any value from empid column in excel matches with empid column values in dataverse. Update the row.
So for that let me know the flow without apply to each.

Manually I checked and I have one value (for eg: 1234) in excel which is appearing 7 times in dataverse records.

First approach

==========

1) I converted excel data with Parse_JSON

2) Used Select1 with one column "CNO" from Parse_JSON

3) List records in dataverse

4) Used Select2 with one column "CNO" from List records in dataverse

5) Now I used Filter array with From as "Select2" and below item()?['CNO'] equals to body('Select2')?['CNO'] but it is throwing error.

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@contains(item()?['CNo'], body('Select_2')?['CNo'])' failed: 'The template language expression 'contains(item()?['CNo'], body('Select_2')?['CNo'])' cannot be evaluated because property 'CNo' cannot be selected. Array elements can only be selected using an integer index.

In Second Approach

=============

1) I converted excel data with Parse_JSON

2) Used Select1 with one column "CNO" from Parse_JSON

3) List records in dataverse

4) Used Select2 with one column "CNO" from List records in dataverse

5) Now I used Filter array with From as "Select2" and below body('Select2') contains gave here hardcoded value (eg;1234) which is available in excel and it gave length of filter array as 735 which is wrong.

Please help in creating this flow correctly.

Thanks.

Categories:

General topics

All responses (16)

Answers (0)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @brk108 ,

To compare two tables/arrays, at least one "Apply to Each" control is required. And to update multiple items in Dataverse, you need another apply to each:

Best regards,
brk108 68 on at

Like
a
(0)

Report
Copy link

Link copied!

List rows in apply to each is taking very long time to run because list rows are 100000.
its taking sometimes 14 hrs. Please let me know how can I reduce time
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @brk108 ,

You can see there is Filter rows applied in the action, so, every time it will get small pieces of records from the table.

Best regards,
brk108 68 on at

Like
a
(0)

Report
Copy link

Link copied!

I want to filter based on a choice column. But filter rows not allowing choice column value.
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @brk108 ,

For Dataverse, in Power Automate Filter rows allows to filter on Choice column Values than Labels:

You can create a hard coding array variable in your flow, filter this array based on the Label and get the corresponding Value, and use this value in Filter rows.

Best regards,
Matthy79 4,188 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @brk108

Of course it is possible to filter those items based on 2 tables without a loop but what data do you need after the filter. I think you somehow want to join those arrays together. Is that the case? And of course you will need an apply to each if you want to update the records. Do you want to update the dataverse table with a hardcoded value or with values from excel or do you want to update excel with data from dataverse or hardcoded values?
brk108 68 on at

Like
a
(0)

Report
Copy link

Link copied!
@Matthy79 I want to find out same excel column value exists in Dataverse table column. If an item exists based on that I want to update. I have two columns 1) CNo-Single Text 2) Client - lookup from ClientTable. For eg:
Cno - 1234 and Client - ABC will be there in excel. If same Item exists in Dataverse I will update that row in Dataverse table.
For that I tried using Filter Array but it is not working .
@and(equals(item()?['dv_cno'], items('Apply_to_each')?['CNo']),equals(item()?['dv_client'], items('Apply_to_each')?['Client']))
Can I write the condition like above. Please guide.
Matthy79 4,188 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry, but I don't understand. If you now need two columns to be compared with, why do you only use a select for column CNO in your first post? And I still don't know what you want to update. This is an important information. You also talk about a lookup column now. In this lookup column there will be a GUID value. Do you want to compare to the primary name column of the lookup table?

Usually the filter array works like this:

@contains(body('Select_simple_array'), item()?['comparecolumn'])
brk108 68 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi sorry for confusion. In simple I have 5 columns in excel . Based on Cno and Client column values combination I must check in Dataverse table . If that row combination is available in Dataverse. I must update the other columns in Dataverse table.

so here how can I compare two excel column combination with Dataverse?

can I use filter array or please suggest some good ways.
Matthy79 4,188 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

It doesn't help that you compare the two columns but if that is what you want I will give you a suggestion.

Create a simple array of your excel rows with "concat(column1,'|',column2)"

After that you can filter your dataverse value array with the formula I already provided modified with 2 columns:

@contains(body('Select_simple_array'), concat(item()?['comparecolumn1'], '|', item()?['comparecolumn2'])