Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
3 years ago
Solved

Combine table with missing records

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. 

Need Help
Show and Tell
Tips and Tricks
  • jbwtp's avatar
    jbwtp
    3 years ago

    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

1 Reply

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    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