Forum Discussion
Show attendance, including zeros
I have three tables:
1. Reference Date table that contains dates 2022 to Today
2. Security Card Swipes for 2022 to Today, which contains employee security card swipes by day
3. List of All Office employees
The ask is to show attendance in the office by individual by day, including the people that didn't attend on that day, and in theory a rollup of attendance over a specific time period.
List of All Employees Sample:
| Jim |
| Lucy |
| Bob |
| Mary |
| Bill |
Security Card Data looks like this:
| April 5, 2022 | Jim |
| April 3, 2022 | Jim |
| April 2, 2022 | Jim |
| April 1, 2022 | Jim |
| April 4, 2022 | Lucy |
| April 3, 2022 | Lucy |
| April 2, 2022 | Lucy |
| April 5, 2022 | Bob |
| April 3, 2022 | Bob |
| April 1, 2022 | Mary |
Here's an example of what it could look like (but I'm open to suggestions):
| Apr 1, 2022 | Apr 2, 2022 | Apr 3, 2022 | Apr 4, 2022 | Apr 5, 2022 | |
| Jim | Y | Y | Y | N | Y |
| Lucy | N | Y | Y | Y | N |
| Bob | N | N | Y | N | Y |
| Mary | Y | N | N | N | N |
| Bill | N | N | N | N | N |
I can't seem to figure out how to get the non-attendance employees by day into a table (potentially like above). Does anyone have any ideas?
The solution contained in Excel file has been uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuVexL5u8RVo-6-vJ?e=4VElax
In Second table, use below code.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Dates(List.Min(Table2[Date]),Duration.Days(List.Max(Table2[Date])-List.Min(Table2[Date]))+1,#duration(1,0,0,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom", "Name"}, Table2, {"Date", "Name"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Name"}, {"Name.1"}), #"Sorted Rows" = Table.Sort(#"Expanded Table2",{{"Index", Order.Ascending}, {"Custom", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Name.1]<>null then "Y" else "N"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name.1"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Custom.1"), #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index"}) in #"Removed Columns1"
2 Replies
The solution contained in Excel file has been uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuVexL5u8RVo-6-vJ?e=4VElax
In Second table, use below code.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Dates(List.Min(Table2[Date]),Duration.Days(List.Max(Table2[Date])-List.Min(Table2[Date]))+1,#duration(1,0,0,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom", "Name"}, Table2, {"Date", "Name"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Name"}, {"Name.1"}), #"Sorted Rows" = Table.Sort(#"Expanded Table2",{{"Index", Order.Ascending}, {"Custom", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom.1", each if [Name.1]<>null then "Y" else "N"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name.1"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Custom.1"), #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index"}) in #"Removed Columns1"