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

(0) Share

Report

Posted on by 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

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (0)

Sort by

lbendlin 8,726 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

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 🙂

Was this reply helpful? Yes No
lbendlin 8,726 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, @lbendlin

@Kryu can edit the columns required based on requirement 🙂

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No