Join two tables based in collunm values.

Question

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&nbsp;&nbsp;&nbsp;TABLE 2&nbsp;&nbsp;EXPECTED TABLE&nbsp;CODEEmployee&nbsp;CODECOURSE&nbsp;CODECOURSECONCLUDED1Jonh&nbsp;1COURSE 1&nbsp;1COURSE 1OK2Marie&nbsp;2COURSE 1&nbsp;2COURSE 1OK3Paul&nbsp;3COURSE 1&nbsp;3COURSE 1OK&nbsp;&nbsp;&nbsp;1COURSE 2&nbsp;1COURSE 2OK&nbsp;&nbsp;&nbsp;1COURSE 3&nbsp;2COURSE 2NOT&nbsp;&nbsp;&nbsp;2COURSE 3&nbsp;3COURSE 2NOT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1COURSE 3OK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2COURSE 3OK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3COURSE 3NOTI thank you in advance for any help.

vijay_a_verma · Answer

Test code for Tbl1
&nbsp;
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"
&nbsp;
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"

Forum Discussion

Related content

Formatting Value of Table

PowerBI - Matrix table, hide values used only for a visual calculation

Table Call Value Formatting Options

Parsing list values in a nested table

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters