Spreadsheet of employee presence information

[Qianru221]

I have a table containing a column of dates.

I have another table containing all employees absence information entries (names, absent from, absent to). Each employee may have multiple lines of entries when they are absent several times in the years from 2016 onwards.

What I would like to have is a combined spreadsheet (1st vertical column is the dates, 1st horizontal row are the names), the space in between is marked as 1 (the employee is absent) and 0 (the employee is present). It should be something which looks like the table below.

What I have achieved so far above is to create a spreadsheet that has 1st vertical column as the dates, 1st horizontal row as unique names of the employees (each employee name should only appears once in the header row). I create an unique namelist and transpose the column to a row. I then append the row with the date column.  However I am not able to have the 0s and 1s filled inside. I also tried with Transform -> pivot columns, but it is not working.

One thing worth noting is that I can't create a measure because I need the spreadsheet to be present appeared in query because later I need to Transform -> Run R script to process the data. Columns created using measures will not appear in Edit Query mode and therefore cannot be used in the R script. Therefore it makes the problem more complicated than expected.

Another way is to run R script in the 2nd picture table to create a spreadsheet using R code. However I also need assistance in writing such a R code....

Is there anyone has any idea on how to achieve that? Many thanks in advance!

[v-yuezhe-msft · Accepted solution]

@Qianru221,

Add blank queries in your PBIX file, then paste the following code into  each Advanced Editor of the blank queries to process the transformation.

Date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdjLjeUwEEPRXHo9gIuUS2XH0uj80xg8zAe8WnLlAxFeFL+/v3Tpcml//fz6BGdYGe4MnWFnmAxPhjeDCgkGASEoBIbgECCCRKAIFsNivgcshsWwGBbDYlgMi2FZsKyw+EJwhpXhztAZdobJ8GR48dFCIggIQSEwBIcAESQCRbAYFvM9YDEshsWwGBbDYliyqJVlrCxjZRkry1hZxsoyVpaxsox18aOFBIOAEBQCQ3AIEEEiUASLYTHfAxbDYlgMi2ExLIaFZSxY8q+5LwRnWBnuDJ1hZ5gMT4YXHy0kgoAQFAJDcAgQQSJQBIthMd8DFsNiWAyLYTEshsWwZFGd1XRW01lNZzWd1XRW01lNZzWd1TSqaVTTqKZRTaOaRjWNahrVNKppVNOoplFNo5pGNY1qGtU0qmlU06imUU0f1eQ/tC8EZ1gZ7gydYWeYDE+GFx8tJIKAEBQCQ3AIEEEiUASLYTHfAxbDYlgMi2ExLIbFsGRRk9VMVjNZzWQ1k9VMVjNZzWQ1k9UMqhlUM6hmUM2gmkE1g2oG1QyqGVQzqGZQzaCaQTWDagbVDKoZVDOoZlDNHNXkP/RcCM6wMtwZOsPOMBmeDC8+WkgEASEoBIbgECCCRKAIFsNivgcshsWwGBbDYlgMi2FZsGRR74XgDCvDnaEz7AyT4cnw4qOFRBAQgkJgCA4BIkgEimAxLOZ7wGJYDIthMSyGxbAYFtxDhVu1cKwWrtXCuVq4VwsHa+FiLZyshZu1eLQWr9bi2Vq8W4uHa/FyLZ6uxdu1eLwWr9dCXZ94vBFVpspUmSpTZapMlak6istfTNwcODpwdeDswN2BwwOXB04P3B6O8eFYH4754dgfjgHiWCCOCeLYII4R4lghjhni2CGOIeJYIo4p4tgijjHiWCOOOeLYI45BAhuEMEIIK4QwQwg7hDBECEuEMEUIW4Q4RohrhDhHiHuEOEiIi4Q4SYibhDhKiKuEOEuIu4Q4TIjLhDhNiNuEOE6I64Q4T4j7xKcNLmyfMH+DM6wMd4bOsDNMhifDm+FPXf8TDAJCUAgMwSFABIlAESyGxXwPWAyLYTEshsWw+J/l5zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

employees absence:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00jcy1zcyMDSDcIwNIJxYnWglJ6i8MbK8IULeGSSkD1JhYGgOYRtD2LGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Abwesend vom" = _t, #"Abwesend bis" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Abwesend vom", type date}, {"Abwesend bis", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Abwesend vom],Duration.Days(Duration.From([Abwesend bis]-[Abwesend vom]))+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Abwesend vom", "Abwesend bis"})
in
    #"Removed Columns"

Merge2:

let
    Source = Table.NestedJoin(#"employees absence",{"Custom"},Date,{"Date"},"Date",JoinKind.RightOuter),
    #"Expanded Date" = Table.ExpandTableColumn(Source, "Date", {"Date"}, {"Date.Date"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Date", "Newname", each if [Name] is null then "Nouser" else [Name]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "check", each if [Custom] is null then null else 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Newname]), "Newname", "check", List.Sum),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns1"

Regards,
Lydia