Forum Discussion

Portrek's avatar
Portrek
Resolver III
3 years ago

Join two tables based in collunm values.

I have a table with the name and code of all company employees; I have another table with the codigo of the employee a column with the training names that are taught within the company, only those who performed the training, and date of accomplishment. I need to unite these tables to identify employees who did or did not do the training. How to do this ? The pictures show a exemples:


TABLE 1   TABLE 2  EXPECTED TABLE 
CODEEmployee CODECOURSE CODECOURSECONCLUDED
1Jonh 1COURSE 1 1COURSE 1OK
2Marie 2COURSE 1 2COURSE 1OK
3Paul 3COURSE 1 3COURSE 1OK
   1COURSE 2 1COURSE 2OK
   1COURSE 3 2COURSE 2NOT
   2COURSE 3 3COURSE 2NOT
      1COURSE 3OK
      2COURSE 3OK
      3COURSE 3NOT


I thank you in advance for any help.

Need Help
Show and Tell

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Test code for Tbl1

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8tQitWJVjICcnwTizJTwTxjIC8gsTRHKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CODE = _t, Employee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", Int64.Type}, {"Employee", type text}})
in
    #"Changed Type"

     

    Test code for Table2

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXL2Dw0KdlUwVIrViVYyQhcwRhdA0mKELmCMbgZQIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CODE = _t, COURSE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", Int64.Type}, {"COURSE", type text}}),
    Custom1 = Table.FromColumns({List.Repeat({Table1[CODE]}, List.Count(Table1[CODE])), List.Distinct(#"Changed Type"[COURSE])}, Table.ColumnNames(Source)),
    #"Expanded CODE" = Table.ExpandListColumn(Custom1, "CODE"),
    #"Added Custom" = [ZipTbl = List.Zip({#"Changed Type"[CODE], #"Changed Type"[COURSE]}), 
result = Table.AddColumn(#"Expanded CODE", "CONCLUDED", each if List.Contains(ZipTbl, {[CODE], [COURSE]}) then "OK" else "NOT")][result]
in
    #"Added Custom"