Show attendance, including zeros

[Anonymous]

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?

[Anonymous]

Amazing Job - this does the trick!

[Vijay_A_Verma · Accepted solution]

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"