SEARCH ITEMS IN OTHER TABLES

[fgonzalez515]

Hello Community,

I have two Excel tables, named DTE and BCO, I want to create a new table that shows me the Items not found or that do not match in their three columns (RUT, FOLIO AND CR).
The search must be done with the DTE Items and look for them in the BCO table.

I leave an example.

[takolota · Accepted solution]

Hello @fgonzalez515 

As long as RUT FOLIO and CR form a unique key that is different for every row in the table…

I would use two Excel List rows actions to get both the tables in Power Automate..

Then use a Select action and in the array From input field, use the intersection expression on the values output of both the Excel List rows, like: Intersection(values(List rows 1), values(List rows 2)). That should generate an array with only the matching values between the two tables. In the Key field just put “Key” and in the value field put item()?[‘RUT’] item()?[‘FOLIO’] item()?[‘CR’] just enter each expression one after the other so it basically concatenates them. That should generate a unique key for each of the matching items in the two tables.

Then use a Filter array action with the BCO table’s Values output in the From array input.

In the filter conditional use String(Body(‘Select’)) Does not contain item()?[‘RUT’] item()?[‘FOLIO’] item()?[‘CR’]

That should check if the list of unique keys from the matching records contains the unique key of each record from the BCO table and only return the records not in the matching records list.

From there you can use a Parse JSON on the Filter array action to get the records into dynamic content.

That should accomplish things in a really efficient way incase you have a lot of records.

[fgonzalez515]

Hi @takolota,

Thanks for the help.

I want to take the obtained result to an HTML table, but it throws me the following error.

How can I solve that?

[takolota]

Hello @fgonzalez515 

Well you would want to use the Append to Array variable not Append to String in that case.

However, I think the Parse JSON body should already have the array set up that way. Did you try just putting the JSON body in the Create HTML From field? Heck, you may not even need the Parse JSON that action may also take the Filter array outputs.

But you may need to change the expressions in the Values column there to select just that Key’s value for each row there.

So…

item()?[‘Rut’]

item()?[‘Folio Dte’]

item()?[‘CR Luciano’]

[fgonzalez515]

Hi @takolota,

Thanks.

This link is helpful

https://www.damobird365.com/efficien-union-except-and-intersect-great-method/ 

[takolota]

Oh nice @DamoBird365 already has some cleaner documentation for this.

That actually may help me make some of my other flows more readable.