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

Question

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

v-juanli-msft · Accepted Answer

Hi&nbsp;TomRichardson&nbsp;
If you'd like this result,&nbsp;

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.

&nbsp;
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"
&nbsp;
&nbsp;
Best RegardsMaggieCommunity Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

Forum Discussion

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

2 Replies

Related content

Table Sort , sort order , comparer

Comparing values between different tables

Compare value in two tables

Compare 2 Tables

Comparing Values between tables