PQ question - dynamically adding rows if a person has not completed training

[scott_86_]

Hi,

In the screen shot below (sorry, I am unable to input a mini sheet due to organisational restrictions), I have tbl_training, tbl_report and tbl_outcome.

tbl_training has a list of training items that all employees are required to complete.

tbl_report has a report of training that employees have completed with a completion date, however, does not have a row for the items in tbl_training that they have not completed.

tbl_outcome is an example of the outcome I am trying to achieve so that all employees also have an additional row(s) row for the items in tbl_training they have not completed with the Date Completed column being left as null.

Would anyone be able to assist in some applied steps to take to achieve this? Thank you.

 

[alansidman]

Since you have not uploaded a file, I can only suggest a solution as I will not attempt to recreate your tables. I would suggest you do an anti join to find the missing training. Once this is completed, then join the original table back to the new anti join. If you want a demo, then post your sample files to a third party location or use XL2BB here. Pictures cannot be manipulated.

 

[smozgur]

I like transforming data on the fly, so this would be my approach:

let
    tblTraining = Excel.CurrentWorkbook(){[Name="tbl_training"]}[Content],
    tblReport = Excel.CurrentWorkbook(){[Name="tbl_report"]}[Content],

    GroupByEmployee = Table.Group(tblReport, {"Employee"}, {{"Data", each _}}),
    AddMissingTrainings = Table.TransformColumns(GroupByEmployee, {"Data",
    each
        let
            Existing = _[Training],
            Missing = List.Difference(tblTraining[Training], Existing),
            AddMissing = Table.InsertRows(_, Table.RowCount(_), List.Transform(Missing, each [Training=_ , Employee ID = null, Employee = null, Date Completed = null, Role = null])),
            Result = Table.FillDown(AddMissing,{"Employee ID", "Employee", "Role"})
        in
            Result
        }),
    RestoreTable = Table.ExpandTableColumn(AddMissingTrainings, "Data", {"Training", "Employee ID", "Date Completed", "Role"}),
    Result = Table.ReorderColumns(RestoreTable,{"Training", "Employee ID", "Employee", "Date Completed", "Role"})
   
in
    Result

Training
AA
BB
CC
DD
Training	Employee ID	Employee	Date Completed	Role
AA	111	Colin	1/1/2023	IT
AA	222	Ricky	1/2/2023	Admin
BB	222	Ricky	1/3/2023	Admin
AA	333	Michael	1/4/2023	IT
BB	333	Michael	1/5/2023	IT
CC	333	Michael	1/6/2023	IT
AA	444	Jane	1/7/2023	Admin
BB	444	Jane	1/8/2023	Admin
CC	444	Jane	1/9/2023	Admin
DD	444	Jane	1/10/2023	Admin
Training	Employee ID	Employee	Date Completed	Role
AA	111	Colin	1/1/2023	IT
BB	111	Colin		IT
CC	111	Colin		IT
DD	111	Colin		IT
AA	222	Ricky	1/2/2023	Admin
BB	222	Ricky	1/3/2023	Admin
CC	222	Ricky		Admin
DD	222	Ricky		Admin
AA	333	Michael	1/4/2023	IT
BB	333	Michael	1/5/2023	IT
CC	333	Michael	1/6/2023	IT
DD	333	Michael		IT
AA	444	Jane	1/7/2023	Admin
BB	444	Jane	1/8/2023	Admin
CC	444	Jane	1/9/2023	Admin
DD	444	Jane	1/10/2023	Admin

 

[scott_86_]

Since you have not uploaded a file, I can only suggest a solution as I will not attempt to recreate your tables. I would suggest you do an anti join to find the missing training. Once this is completed, then join the original table back to the new anti join. If you want a demo, then post your sample files to a third party location or use XL2BB here. Pictures cannot be manipulated.

Hi, I think I have figured out using XLB22. Please see below.

Looking forward to learning more about anti join (first time I have heard of it was in your post!)

Book1
	A	B	C	D	E	F
1	Table Name =	tbl_training
2	Training	Training ID
3	AA	AA11
4	BB	BB22
5	CC	CC33
6	DD	DD44
7
8
9	Table Name =	tbl_report
10	Training	Training ID	Employee ID	Employee	Date Completed	Role
11	AA	AA11	111	Colin	01/01/2023	IT
12	AA	AA11	222	Ricky	01/02/2023	Admin
13	BB	BB22	222	Ricky	01/03/2023	Admin
14	AA	AA11	333	Michael	01/04/2023	IT
15	BB	BB22	333	Michael	01/05/2023	IT
16	CC	CC33	333	Michael	01/06/2023	IT
17	AA	AA11	444	Jane	01/07/2023	Admin
18	BB	BB22	444	Jane	01/08/2023	Admin
19	CC	CC33	444	Jane	01/09/2023	Admin
20	DD	DD44	444	Jane	01/10/2023	Admin
Sheet2

 

[AlienSx]

let
    tr_list = List.Buffer(tbl_training[Training]), 
    report = Table.Group(
        tbl_report, 
        "Employee ID", 
        {"x", (x) => x & #table({"Training"}, List.Zip({List.RemoveItems(tr_list, x[Training])}))}
    ), 
    result = Table.FillDown(Table.Combine(report[x]), List.RemoveItems(Table.ColumnNames(tbl_report), {"Date Completed"}))
in
    result

 

[smozgur]

let
    tr_list = List.Buffer(tbl_training[Training]),
    report = Table.Group(
        tbl_report,
        "Employee ID",
        {"x", (x) => x & #table({"Training"}, List.Zip({List.RemoveItems(tr_list, x[Training])}))}
    ),
    result = Table.FillDown(Table.Combine(report[x]), List.RemoveItems(Table.ColumnNames(tbl_report), {"Date Completed"}))
in
    result

Beautiful! Thanks for sharing. I now learned that I can apply the transformation with the Table.Group function instead of an extra Table.TransformColumns step!

 

[scott_86_]

let
    tr_list = List.Buffer(tbl_training[Training]),
    report = Table.Group(
        tbl_report,
        "Employee ID",
        {"x", (x) => x & #table({"Training"}, List.Zip({List.RemoveItems(tr_list, x[Training])}))}
    ),
    result = Table.FillDown(Table.Combine(report[x]), List.RemoveItems(Table.ColumnNames(tbl_report), {"Date Completed"}))
in
    result

Hi AlienSx,

Thanks very much for that. I just replied to your post a few minutes ago asking a question on specifically how to apply the code within the advanced editor; however, I worked it out so am now editing my post.

 

[scott_86_]

let
    tr_list = List.Buffer(tbl_training[Training]),
    report = Table.Group(
        tbl_report,
        "Employee ID",
        {"x", (x) => x & #table({"Training"}, List.Zip({List.RemoveItems(tr_list, x[Training])}))}
    ),
    result = Table.FillDown(Table.Combine(report[x]), List.RemoveItems(Table.ColumnNames(tbl_report), {"Date Completed"}))
in
    result

Hi AlienSx.

Furthermore to my above post confirming this works, would it be possible to also reference the below tbl_employees table to include employees who have not completed any of the tbl_training items within tbl_report?

So in this case the result would include 8 extra rows for the 2 people people who have not completed any training at all. 4 for Eddie and 4 for Clare - with a null value in the Date Completed column.

Book2
	A	B	C
9	Table Name =	tbl_employees
10	Employee ID	Employee	Employee Type
11	111	Colin	Full-time
12	222	Ricky	Full-time
13	333	Michael	Full-time
14	444	Jane	Full-time
15	555	Eddie	Casual
16	666	Clare	Casual
Sheet1