Comparing Row in Dataverse table and Excel table to update excel table or create a new entry. What´s the best way?

[Lafsen]

I´m stuck on comparing two tables. Both have a identifier column named MasterOrderDataID with Unique IDs per row.

I want to get data from a dataverse table called MasterOrderData and update the data in an excel file. If the ID exists in the excel I want to update the row with the newest info. If it does not exist then create a new entry.

My struggle is to compare the tables in the most efficient way which I assume is to:

- Go through each ID in MasterOrderData and match it with the IDs in the excel.

Now PowerAutomate forces me to a nested "apply to each" in every scenario which makes it inefficient. How can I do this with 1 "apply to each" loop?

Thanks!

[rzaneti]

Hi @Lafsen ,

I was playing around to try to find an efficient way for doing that. I found a possible solution here that probably may help you (it avoids the inner loop).

For this example, I used the both tables below. I will iterate the table on the right to identify if the ids exists or not in the left table (only two of then do not exists). Notice that you can replicate the same logic to your Excel + Dataverse tables.

First, I access all values from both tables:

Then I loop the 'id_only' table ('List rows present in a table 2' action, highlighted in yellow) and, inside the loop, filter the 'Office_list' table ('List rows present in a table' action, highlighted in blue). For the filter operation, I'm comparing the Ids, so the 'Office_list' table will be filtered by the id of the current iteration of the 'id_only' table:

As we are handling a column with unique values, the filtered array will have only 1 or zero records. So after the 'Filter array' action, we add a 'Condition' to check the length of its output: if contains zero records, you will add it to your Excel table, if contains something different from zero (in this case, 1 record), you will update your Excel table: 

Expression: length(body('Filter_array'))

When running the flow, as I'm testing if the 'Filter array' output length is equal to zero, the condition will return 'true' only for the Ids that are not present in the 'Ofifce_list' table:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[Lafsen · Accepted solution]

thanks for your efforts, really appreciate your time! It just seems like such an elaborate solution for a simple match situation. 

After doing some more research I found the best way was to use Select command on the data source I wanted to compare towards to avoid that second "Apply to Each". Works exactly like I want it now!