Join datatable values but not update the value for duplicate

[aqiffm]

I have two datatable, example as follows:

Table 1:
ID | Name
1 | John |
2 | Peter |
3 | Mary |
4 | Paul |
5 | Logan |
6 | Mary |
7 | Stacy |

Table 2:
ID | Name | Country |
1 | John | USA |
2 | Peter | UK |
3 | Mary | JPN |

Table 3:
ID | Name | Country | Duplicate |
1 | John | USA | |
2 | Peter | UK | |
3 | Mary | JPN | |
4 | Paul | | |
5 | Logan | | |
6 | Mary | | Duplicate |
7 | Stacy | | |

How can I achieve to get table 3 without using for each row? (Real use case has 700k rows of data)

[rlgandu (Rajyalakshmi Gandu)]

@aqiffm

(From t1 In dtTable1.AsEnumerable()
 Group Join t2 In dtTable2.AsEnumerable()
 On t1("Name") Equals t2("Name") Into Group
 Let Country = If(Group.Any(), Group.First()("Country").ToString(), "")
 Let Duplicate = If(dtTable1.AsEnumerable().Count(Function(x) x("Name").ToString() = t1("Name").ToString()) > 1, "Duplicate", "")
 Let ID = t1("ID").ToString()
 Let Name = t1("Name").ToString()
 Select dtTable3.Rows.Add({ID, Name, Country, Duplicate})).
 DistinctBy(Function(row) row("Name").ToString()).
 CopyToDataTable()

Sequence23.zip (2.3 KB)
Output:

[lrtetala (Lakshman Reddy)]

Hi @aqiffm

Can you try the below

Main.xaml (15.2 KB)

Output:

Regards,