Select from table1 where not in select from table2

[jimrward]

for example I have a table1 which contains member names and other information , I also have a table2 which contains a list of names and a column called IsCurrent which is a simple Y/N
I am trying to identify new members in table1 that are not in table2 where IsCURRENT=“Y”
In rough sql it might be

Select name from table1 where name not in (select name from table2 where iscurrent=“Y”)

I can then check these new names have paid their membership and add to table2

 

[smozgur]

Source tables: Table1 and Table2

Book1
	A	B	C	D	E
1	Member Name	Other Info		Member Name	IsCurrent
2	a	123		a	Y
3	b	234		c	Y
4	c	456		d	N
5	d	567
6	e	678
Sheet1

Assuming the "Member Name" column contains unique values in Table2 (or you'll probably use an ID column instead).

M Code goes to PQ->New Blank Query->Advanced Editor:

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    FilterTable2 = Table.SelectRows(Table2, each [IsCurrent] <> "Y"),
    Join = Table.Join(Table1, {"Member Name"}, FilterTable2, {"Member Name"}),
    Result = Table.RemoveColumns(Join,{"IsCurrent"})
in
    Result

Make sure using the corresponding table and columns names, then you get the matched rows.

Book1
	A	B
1	Member Name	Other Info
2	d	567
Result

 

[jimrward]

Many thanks for that I will try it later
I was thinking along those lines but I wasn’t sure if a join operation would be applied to the raw table or the last known state ie filtered in this case, so I didn’t know how to apply the iscurrent=“y”