Kusto - Join two tables and count keys from first table and second table on every record from first table

Question

Need to Join two tables and count key from first table and second table on every record from first table

let T = datatable(TId:int, TName:string, Tkey:string)
[
   1, "A", "xyz",
   2, "B", "xyz",
   3, "C", "yza",
];
let u = datatable(UId:int, UName:string, Ukey:string)
[
   1, "bla1", "xyz",
   2, "bla2", "xyz",
   30, "bla3", "xyz",
   12, "bla5", "xyz",
   80, "bla9", "xyz",
   4, "bla11", "cde",
   55, "bla12", "yza",
   96, "bla21", "yza",
];

Expected result

Answer 1

let T = datatable(TId:int, TName:string, Tkey:string)
[
   1, "A", "xyz",
   2, "B", "xyz",
   3, "C", "yza",
];
let u = datatable(UId:int, UName:string, Ukey:string)
[
   1, "bla1", "xyz",
   2, "bla2", "xyz",
   30, "bla3", "xyz",
   12, "bla5", "xyz",
   80, "bla9", "xyz",
   4, "bla11", "cde",
   55, "bla12", "yza",
   96, "bla21", "yza",
];
let T_sum = T | summarize TkeyCount = count() by Tkey;
let u_sum = u | summarize UkeyCount = count() by Ukey;
T
| extend Ukey = Tkey
| lookup kind=leftouter T_sum on Tkey
| lookup kind=leftouter u_sum on Ukey

TId	TName	Tkey	Ukey	TkeyCount	UkeyCount
1	A	xyz	xyz	2	5
2	B	xyz	xyz	2	5
3	C	yza	yza	1	2

Fiddle