cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomRichardson
New Member

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

Hi @TomRichardson 

If you'd like this result, 

Capture1.JPG

First, add a merged column,

Capture2.JPG

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

Capture3.JPG

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

finally, add a conditional column.

Capture4.JPG

 

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.

 

View solution in original post

2 REPLIES 2
TomRichardson
New Member

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

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

Community Support
Community Support

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

Hi @TomRichardson 

If you'd like this result, 

Capture1.JPG

First, add a merged column,

Capture2.JPG

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

Capture3.JPG

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

finally, add a conditional column.

Capture4.JPG

 

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.

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors