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

Question

Help please!!&nbsp;So i have table 1&nbsp;NameTrue FalseJohn&nbsp;Mary&nbsp;Susan&nbsp;Adrian&nbsp;&nbsp;&nbsp;Table 2NameWork DoneDate CompleteJohnWork A01/01/23MaryWork B02/01/23JohnWork B03/01/23SusanWork A05/01/23JohnWork C01/01/23AdrianWork A06/01/23&nbsp;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 &amp; C.&nbsp;So my PowerQuery needs to be able to read 'John' from Table one, match it to Table to and check if A, B &amp; C have been recorded.&nbsp;Thank you in advance for any help. &nbsp;

ronrsnfld · Answer

Do you really need that first table?&nbsp;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"&nbsp;&nbsp;

alavery · Answer

Thank you so much for your reply. &nbsp;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. &nbsp;But I will work with what you have given me and see what I can make from it. &nbsp;Thanks so much for taking the time.&nbsp;

ronrsnfld · Answer

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.

ronrsnfld · Answer

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.&nbsp;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.&nbsp; This can be done from the UI. Navigate to Home=&gt;Combine=&gt;Merge Queries.&nbsp;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"

Forum Discussion

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

4 Replies

Related content

Reference one notebook from another

Reference a measure in another measure

Yet another Sort by another Column error.

Copy entire Lakehouse to another Workspace

Sort by another column

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