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

[TomRichardson]

I have two sets of data out of CRM. Active Employees lists all of our staff regardless of time reported. Active Employee Hours only lists hours that have been reported. I want to build a report that shows only the employees that do not have time reported for a given day.

I think what I'd like is a column that checks for Employee Name and all Time Entry Dates and returns a False value if no record or hours exist for the given date.

Any ideas?

Active Employees

• Columns:
  • Employee name

Active Employee Hours

• Columns:
  • Employee name
  • Time Entry Date
  • Time Reported

[TomRichardson]

For example:

Table 1:

Joe Smith

Betty Crocker

Table 2:

Joe Smith / 11/18/19 / 5 hours

Joe Smith / 11/19/19 / 8 hours

Betty Crocker / 11/19/19 / 8 hours

New column: for 11/18/19 return False value

[v-juanli-msft · Accepted solution]

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.