Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
Portrek
Resolver III
Resolver III

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.

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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"

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.