How to get the intersection of two datatable in power automate desktop ?

[Kryu]

I have two datatables extracted from excel.

Now, I need to get the data that is the same between two tables. which means intersection.

 But I can't find an available function in PAD.

Any advice?

 Thanks

[lbendlin]

You can only do that with lists, not with tables.

You would have to concatenate all columns in a row to a string and then treat the result as a list of strings.

There is this 

but it works on columns, not rows. Comparing column by column defeats the purpose.

There is no pivoting action as far as I am aware.

[Agnius]

There is no native function to do that in PAD. But, assuming you are matching the tables based on some Id column or something like that, you could use Retrieve data table column into list to get that column from both of your tables, then use Find common list items to compare the two lists, and then build some loops, where you use the Find or replace in data table action to find the row indexes for each matched item in both tables. 

This might be a bit slow for large tables, but it should generally do the trick and allow you to proceed with whatever you want to do next with the matched items.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Nived_Nambiar]

Hi @Kryu 

Another way would be use to connect excel as database and use sql queries to perform 

But this would only possible if you add the second excel sheet data into the first excel as another sheet 

So first excel file would have 2 sheets : Sheet1 and Sheet2

Now connect to excel using sql connection and use inner join to join two tables based on a column or combination of 2 or more column conditions. Take this as an example

I have an excel file having two sheets

See below flow on how to do inner join 

SQL query used :  SELECT table1.A,table1.B,table1.C FROM [Sheet1$] as table1 INNER JOIN [Sheet2$] as table2 on table1.A=table2.A

Output of this sql query would be : 

[But you want to make the power automate flow in such a way that before performing this sql operation, add the second excel file data to first excel file as another sheet and once sql query is performed, delete that newly added sheet if needed ]

I know it would be little complex at design part, But you can give a try and see if it gives you expected results or not 🙂

Hope it helps 

@Agnius  @lbendlin  let me know your thoughts on this as well 🙂

[lbendlin]

The join condition in step 3 would have to include all columns.  Other than that - nice workaround.

[Nived_Nambiar]

Yes, @lbendlin 

@Kryu  can edit the columns required based on requirement 🙂

[Agnius]

Yes, this is also an option. It does require extra drivers to be installed on the machine. See here for reference: https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel