Add column to table that references another table and checks another column for entries

[alavery]

Help please!!

So i have table 1

Name	True False
John
Mary
Susan
Adrian

Table 2

Name	Work Done	Date Complete
John	Work A	01/01/23
Mary	Work B	02/01/23
John	Work B	03/01/23
Susan	Work A	05/01/23
John	Work C	01/01/23
Adrian	Work A	06/01/23

I want to be able to add True/False in the True False column of table 1 if the corresponding name in Table 2 has completed Work A, B & C.

So my PowerQuery needs to be able to read 'John' from Table one, match it to Table to and check if A, B & C have been recorded.

Thank you in advance for any help.  

[ronrsnfld]

Do you really need that first table?

Without it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQrPL8pWcAQyDAz1gcjIWClWJ1rJN7GoEibpBJI0QpJE1gmWNEaSDC4tTkQ11xSHVmd0Sx1TijLR9JrBpGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Work Done" = _t, #"Date Complete" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, 
        {"Work Done", type text}, {"Date Complete", type date}}),

    #"All Works" = List.Distinct(#"Changed Type"[Work Done]),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
        {"True False", each List.ContainsAll([Work Done],#"All Works"), type logical}})
in
    #"Grouped Rows"

[alavery]

Thank you so much for your reply.  Unfort I do need the first table as it is used as a lookup/reference point and the second table currently has over 3000+ entries.  But I will work with what you have given me and see what I can make from it.  Thanks so much for taking the time.

[ronrsnfld]

If your "Name" table (Table 1) is in your Queries list as a second query, all you need to do is Merge Queries using Table 1 and the Table 2 query I showed above.

Depending on the order in which you select the queries, do either a Right or Left OuterJoin. This can give you the lookup results with nulls opposite names in Table 1 that do not exist in Table 2.  This can be done from the UI. Navigate to Home=>Combine=>Merge Queries.

If the coded query above is named "Table 2", and your list of names is named "Table 1", then the following code is generated:

let
    Source = Table.NestedJoin(#"Table 2", {"Name"}, #"Table 1", {"Name"}, "Table 1", JoinKind.RightOuter),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name"}),
    #"Expanded Table 1" = Table.ExpandTableColumn(#"Removed Columns", "Table 1", {"Name"}, {"Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table 1",{"Name", "True False"})
in
    #"Reordered Columns"

[ronrsnfld]

In that case you should be able to create the second table as I've shown, and just do a Left (or Right) OuterJoin with Table 1, with the Name column in each being the key and deciding what you want to do with Names in Table 1 that don't exist in Table 2

Don't have time to do that just now, but will later if you can't figure it out.