Filter table by column value and compare with another table column to find missing values

[Anonymous]

Hello

I have two tables - the first one with the list of names who have sent their sales data based on the week number:

And another table with the reference list of all names:

These two tables are updated constantly so the process should be dynamic. The goal is to have a table with the list of names which have not submitted their sales data for each week number from the first table. I think maybe the best way is to create a new dynamic table with two columns with the week number from the first table and the list of names who have not submitted for that week number. I am new to using Power Query and Power BI but from my research and understanding, i should use Power Query instead of DAX to achieve this?

So, to achieve the desired result, my approach (please feel free to correct me if there is a better or more efficient way) would be to first sort by week number and then make a list of those names and then compare the list of names with the reference list of names and then have a new table created with the list of names which are missing from the sales table. Is this method or way of thinking is correct, then i have no idea how to proceed. What M code to use?

Thanks in advance for any help!!

[amitchandak]

@Anonymous , Usually Power Query is bit faster, that is why we prefer it. In this case DAX can also suite, As dax has some direct functions

new table in dax =

except(crossjoin(distinct(Table1[week]), Table2[Reference List of name]), summarize(Tabel1, Table[Week] Table2[Name]) )