Compare two tables and display missing data

I have hundreds of lines with different courses that my employees took during the year. And I also have another table with the mandatory courses they have to take... I want to find a way to compare those two tables and see what courses are missing. I would also appreciate if someone help me giving me ideas to perform a "completion bar" (example: employee 1: 78% courses taken)

To illustrate a little bit the data that I have:

Table 1(TRAINING 2021)

Bruno | Course A
Bruno | Course B

Table 2(MANDATORY TRAINING 2021)

Course A
Course B
Course C

Desired output (missing courses):

Bruno | Course C

Thanks a lot.

CodePudding user response：

See the comments in the code, and explore the Applied Steps window, to understand the algorithm

Training2021

MandatoryTraining2021

M Code

let
    Training2021= Table.FromRecords(
        {[Name="Bruno", Course="Course A"],
        [Name="Bruno", Course="Course B"],
        [Name="George",Course="Course A"],
        [Name="George",Course="Course C"],
        [Name="Sandra",Course="Course C"]},
        type table [Name=Text.Type, Course=Text.Type]),
    MandatoryTraining2021= Table.FromRecords(
        {[Course="Course A"],
        [Course="Course B"],
        [Course="Course C"]},
        type table[Course=Text.Type]),

//group training table by Name
    group = Table.Group(Training2021,"Name",
                {"Courses", each [Course]}
            ),

//Missing courses using List.RemoveMatchingItems
    #"Added Custom" = Table.AddColumn(group, "Missed", each 
            Text.Combine(
                List.RemoveMatchingItems(
                    MandatoryTraining2021[Course],[Courses]),"; "), type text),

//Percent Completed
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "% Taken", each 
        List.Count([Courses])/Table.RowCount(MandatoryTraining2021),Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Courses"})


in
    #"Removed Columns"

Results

CodePudding user response：

Please check this solution https://community.powerbi.com/t5/Desktop/Finding-differences-between-tables/m-p/328736#M146789

Regards