Forcing null values into table

[MattyP]

I am attempting to create a report that tracks training done by employees and reporting a percentage of training currency vs non-current. Unfortuantely, the root source only reports a status of someone's training if they've ever done the training at least once; if they've never accomplished the training, it simply does not report that training at all. As an example, you can see below where Lee has 6 diffent kinds of training, but 'Mond only has four, because 'Mond hasn't done the Brandon Act or the Controlled Unclassified training, so the backend just doesn't report it.

I'd like to be able to force a table to look for values for each training course against every individual, to pull the date completed from anything that is reported, and supply a null value if there isn't a correlated date supplied (e.g., load all 6 courses against every employee, and report either a date or a null value from the source data.) Is there a way to do this?

[Chewdata · Accepted solution]

Hey! 

This is certainly possible! What you need is a carthasian product. This is done merging two tables (one with a list of employees and one with list of courses. 
We give both tables a colum with MergeId = 1. So everything joines with everything.
After that, we join the training data to this table. See the queries below:

Table Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nF1VdJRCilKzMzLzEtXcARyDAx1gcjIwMhEKVYHQ4UTSIWxroERThXOIBWmugYmCBVBjpG+/n4uGDaZopiDRRWIY2iiC7QQrsrd1T/IHdNJhihGYSoCucrQmAhFxoYErAO5Ceg5uEmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Training = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Training", type text}, {"Date", type date}})
in
    #"Changed Type"

Table Employees:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnf1D3J3VYrViVYKcoz09fdzAbN9XIFisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}}),
    add_MergeId = Table.AddColumn(#"Changed Type", "MergeId", each 1, Int64.Type)
in
    add_MergeId

Table Training:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzMzLzEtXcFSK1UHiOqFynVG5LkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Training = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Training", type text}}),
    add_MergeId = Table.AddColumn(#"Changed Type", "MergeId", each 1, Int64.Type),
    leftjoin_Employees = Table.NestedJoin(add_MergeId, {"MergeId"}, Employees, {"MergeId"}, "Employees", JoinKind.LeftOuter),
    #"Expanded Employees" = Table.ExpandTableColumn(leftjoin_Employees, "Employees", {"Employee"}, {"Employee"}),
    leftjoin_Data = Table.NestedJoin(#"Expanded Employees", {"Training", "Employee"}, Data, {"Training", "Employee"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(leftjoin_Data, "Data", {"Date"}, {"Date"})
in
    #"Expanded Data"