Datatable Joins

[WorkingForFood]

I have two DataTable variables in PAD and need to join them. Is there a way to do this in PAD without using a SQL database?

[ZS440]

@WorkingForFood 

The number of columns needs to be the same.

try it sample.

SET NewVar TO {[1, 2, 4], [6, 7, 8] }
SET NewVar2 TO {[8, 2, 4], [6, 7, 8], [5, 6, 7] }
LOOP LoopIndex FROM 0 TO NewVar2.RowsCount - 1 STEP 1
SET NewVar TO NewVar + NewVar2[LoopIndex]
END

[WorkingForFood]

Thanks for the reply @ZS440 , but it looks like your solution produces a UNION, but I'm looking for a JOIN (specifically LEFT JOIN).

[cfoenki]

Hi @ZS440 , I know that this is an old post but your solution would be exactly what I need.

But I failed to apply it.

When I apply the last Set variable action, it is like this:

On your screen you don't have the ' among the +.

How did you get that?

Thank you in advance.

[ZS440]

@cfoenki 

%NewVar% + %NewVar2[LoopIndex]%

Note the "%" and try writing

%NewVar + NewVar2[LoopIndex]%

[cfoenki]

You're my hero! 🙂

[MitheshRpa]

Can i have this for inner join??

I have 2 data tables 
Dt1 Column data 1 , 2 ,3,5,6,7

DT2  Column data 3, 4,6

I want result as 3 , 6 in my output DataTable column

[Nived_Nambiar]

There is no direct way , What would i suggest here is

1. First find the datatable which has lowest number of rows [ for faster iteration]

2. loop through that datatable [smallest rows] and then use find or replace action to find that column value in other datatable and then insert that row into new datatable

Hope this helps !!

[Pavel_NaNoi]

If you have excel you can also easily just write each column via a for each into excel and then read that table, resulting in a fully built and combined data table variable.

[Nived_Nambiar]

Hi @Pavel_NaNoi 

I didn't get your point

How does writing into excel would help in joining?

[Pavel_NaNoi]

If you write each column into a separate column e.g.:

-Write datatable of size 3 columns into Excel in row 1 column 1

-Write datatable 2 of size 1 column into Excel in row 1 column 4

-Read Excel

this will create a new datatable 3 variable with both of those tables together as excel reads them literally.

[ZS440]

@MitheshRpa 

Hi Mithesh.

Plz try SQL on Excel.

maybe easy.

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel