Combine table with missing records

[Applicable88]

Hello,

I have a mastercalendar and a facttable. The mastercalendar consist of all dates I need and also icludes a "Productive" indicator to show if a day is a working day or not. In the facttable I got all the records of task which have been done. 
Here are my two example tables:

Mastercalendar:

Date		Productive		Month
08.11.2022		1		11
09.11.2022		1		11
10.11.2022		1		11
11.11.2022		1		11
12.11.2022		0		11
13.11.2022		0		11
14.11.2022		1		11
15.11.2022		1		11
16.11.2022		1		11

Facttable:

Task		Date
Washing		2022-11-08
Cleaning		2022-11-08
Vacuum		2022-11-08
Cleaning		2022-11-09
Washing		2022-11-10
Vacuum		2022-11-10
Washing		2022-11-16

All three task needs to be done on every "Productive =1" day: Washing, Cleaning and Vaccum. I need a report where I check if all three things have been done, or which task of a daily task is missing. As you can see on 2022-11-08 all three task have been done, on

2022-11-09 only Cleaning was done, on 2022-11-10 Cleaning is missing and so fourth.... on "ProductiveDay =0" I don't need a check at all. 

When i doing a left join with PQ I can join all dates with mastercalendar but how to check for the missing task? The best would be a Flag which indicates that on a productive day which Task haven't been done, but also don't check for the unproductive days.

Thank you very much in advance. 

Best. 

[jbwtp · Accepted solution]

Hi @Applicable88,

Please try this:

let 
    Mastercalendar = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDQMzTUMzIwMlLSUTIEYUOlWB2guCV2cUMDHOKGOMSNkMQNkMSNcYib4DDHFIe4GRbxWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Productive = _t, Month = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Productive", Int64.Type}, {"Month", Int64.Type}})
        in
            #"Changed Type",

    Facttable = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8szsjMS1fSUbLQNzTUNzIwMlKK1YlWcs5JTczDJhGWmFxamotPvSWKBMICQwPsBqGJI2kwQ0jEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Date = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Date", type date}})
        in
            #"Changed Type",
    allTask = List.Distinct(Facttable[Task]),

    FirstFilterAllProductive = Table.SelectRows(Mastercalendar, each [Productive] = 1),
    AddTasks = Table.NestedJoin(FirstFilterAllProductive, {"Date"}, Facttable, {"Date"}, "Tasks", JoinKind.LeftOuter),
    #"Expanded Tasks" = Table.ExpandTableColumn(AddTasks, "Tasks", {"Task", "Date"}, {"Task", "Date.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Tasks", {"Date"}, {{"Completed", each List.ContainsAll(_[Task], allTask), Logical.Type}, {"MissedList", each List.RemoveItems(allTask, _[Task]), type list}, {"MissedText", each Text.Combine(List.RemoveItems(allTask, _[Task]), ", "), type text}})
    

in #"Grouped Rows"

Cheers,

John