Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Active Employee Hours
Solved! Go to Solution.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.