Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors