Compare Values in Two Tables and Return False if Data Does Not Exist

Hi TomRichardson 

If you'd like this result, 

First, add a merged column,

Second, add a datelist in "Active Employee Hours" table,

Next, merge two queries, then expand the columns from the merged queries,

finally, add a conditional column.

The whole code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVQjOzSzJUNJRMjTUN7TQNzIwtARyTBUy8kuLipVidTAVWcIUWSApckotKalUcC7KT85OLcKpMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee name" = _t, #"Time Entry Date" = _t, #"Time Reported" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee name", type text}, {"Time Entry Date", type date}, {"Time Reported", type text}}),
    //add a merged column
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged1", each Text.Combine({[Employee name], Text.From([Time Entry Date], "en-US")}, ","), type text),
    // create a date list
    datelist = Table.SelectColumns(#"Inserted Merged Column","Time Entry Date"),
    #"Removed Duplicates_datelist" = Table.Distinct(datelist),
    #"Added Custom_datelist" = Table.AddColumn(#"Removed Duplicates_datelist", "Custom", each #"Active Employees"),
    #"Expanded Custom_datelist" = Table.ExpandTableColumn(#"Added Custom_datelist", "Custom", {"Employee name"}, {"Custom.Employee name"}),
    #"Inserted Merged Column1" = Table.AddColumn(#"Expanded Custom_datelist", "Merged2", each Text.Combine({[Custom.Employee name], Text.From([Time Entry Date], "en-US")}, ","), type text),
    // merge two queries and expand
    #"Merged Queries" = Table.NestedJoin( #"Inserted Merged Column1", {"Merged2"}, #"Inserted Merged Column", {"Merged1"},"merge", JoinKind.LeftOuter),
    #"Expanded merge" = Table.ExpandTableColumn(#"Merged Queries", "merge", {"Time Reported"}, {"merge.Time Reported"}),
    // add conditinal column
    #"Added Conditional Column" = Table.AddColumn(#"Expanded merge", "Custom", each if [merge.Time Reported] = null then false else true)
in
    #"Added Conditional Column"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.