Compare if values in a column are found in another table

[shermayne123]

Hi,

I am trying to create a column "Purchase Type" in a Purchase table by checking if the values in one of the columns(Suppliers) of this table appear in a column(Supplier Code) in another table(IT Suppliers). If it does, then enter " IT Purchase" in the row, else enter "Non-IT Purchase".  

I have tried various methods 1) create conditional column 2) create custom column 3) create new column but all without getting the correct values. 

My code is : 

Purch Type =

IF(

    ('All FYs'[(CON) Supplier (for Services)] = 'IT Suppliers'[Supplier Code],

    "IT Purchase",

    "Non-IT Purchase"

)

I also tried:

Table.AddColumn(#"Renamed Columns", "Purchase Type", each if [#"(CON) Supplier (for Services)"] = #"IT Suppliers"[Supplier Code] then "IT Purchase" else "Non-IT Purchase")

Please help advise what is the right code/method/steps to use, thank you so much!

[shermayne123]

Thank you amitchandak for the swift response and the code! After adding a missing comma, it works correctly now. 

Purch Type =
IF(
not(isblank(Countx(filter('IT Suppliers', 'All FYs'[(CON) Supplier (for Services)] = 'IT Suppliers'[Supplier Code]),'IT Suppliers'[Supplier Code]))),
"IT Purchase",
"Non-IT Purchase"

)

Thank you so much!!

[amitchandak · Accepted solution]

@shermayne123 , Try a new column like

Purch Type =
IF(
not(isblank(Countx(filter('IT Suppliers', 'All FYs'[(CON) Supplier (for Services)] = 'IT Suppliers'[Supplier Code]),'IT Suppliers'[Supplier Code])))
"IT Purchase",
"Non-IT Purchase"

)