Error if no match on join

[dzzxyzz]

Hi, I am having trouble setting up a power query such that the power query will fail if there are any rows in my first table that do not match with any rows in a second lookup table, for a left join.

To provide more context, I have a 'main table' that has Cost Center as a column, and I want to join to a lookup table for Cost Centers to pull in columns related to the Cost Center. The main table points to an Excel file that automatically gets refreshed (i.e. the file gets replaced) on a regular basis, so that my dashboard can also refresh easily. But, if there are any new Cost Centers that show up in the main table's Excel file that are not in the lookup table, I want the dashboard to throw an error so that I can manually investigate / add the required rows to the lookup table (as opposed to excluding those rows by using an inner join, for example).

BONUS: On a similar vein, I was wondering if there's a way to also throw an error if a table has a particular column with duplicate values (e.g. if the Cost Center lookup table had duplicate rows for Cost Center).

[Vijay_A_Verma]

Problem 1 - If there are any new Cost Centers that show up in the main table's Excel file that are not in the lookup table, I want the dashboard to throw an error

When the correspoding values don't exist in Lookup table, all values from Lookup table will be null. 

Then you can check for the existence of the null in any field of your choice (choose a field which can't be null) and mark error in the custom column.

Note, you can also check just before expanding your result by Table.IsEmpty as well.

if Table.IsEmpty([Column] then "Fail" else "Pass"

2. if there's a way to also throw an error if a table has a particular column with duplicate values

You will need to group the table and take count and do a self merge to take count in a separate column. 

Create a custom column to say if count > 1, then duplicate.

Hence, in a custom column you can say

= if [Column]=null then "Fail" else "Pass"

Edit - Another approach is to use following

if List.Count(List.PositionOf(Source[Letters],[Letters],Occurrence.All))>1 then "Duplicate" else "Not Duplicate"

All these techniques I have demonstrated in following file - https://1drv.ms/x/s!Akd5y6ruJhvhuSOtm_KabLBuzL7F?e=34HFkh 

[dzzxyzz]

Hi Vijay, how do I "mark errors" in the custom column? Tried to google but couldn't find the syntax for specifying errors.

Here are my applied steps in the query currently:

And here is my code in the Advanced Editor:

I also don't understand your "other approach" - where exactly would I be putting this line of code with the if statement?

[Vera_33]

Hi @dzzxyzz 

When you do a left outer join, add a custom column to count the rows of the lookup table, if it is 0 then no row in lookup table, if it is greater than 1 then duplicated rows in lookup table

Table.RowCount([lookupTable])