Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredGet Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now
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 | ||||||
| CODE | Employee | CODE | COURSE | CODE | COURSE | CONCLUDED | ||
| 1 | Jonh | 1 | COURSE 1 | 1 | COURSE 1 | OK | ||
| 2 | Marie | 2 | COURSE 1 | 2 | COURSE 1 | OK | ||
| 3 | Paul | 3 | COURSE 1 | 3 | COURSE 1 | OK | ||
| 1 | COURSE 2 | 1 | COURSE 2 | OK | ||||
| 1 | COURSE 3 | 2 | COURSE 2 | NOT | ||||
| 2 | COURSE 3 | 3 | COURSE 2 | NOT | ||||
| 1 | COURSE 3 | OK | ||||||
| 2 | COURSE 3 | OK | ||||||
| 3 | COURSE 3 | NOT |
I thank you in advance for any help.
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"
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.