Create custom function with IF statement to handle null values

[jmpmolegraaf]

Dear all,

Thanks for taking your time to assist me. I have the following custom function that is called in another query.

the function (below) is called in a query as follows: 
= Table.AddColumn(#"Invoked fAddCostBulk", "fPackCost", each try fPackCost([Ex_Plant], [Packaging_OW], [Product Name_OW], [Pricing Date]) otherwise null)

function:

(source_plant as text, packaging as text, additive as text, pricing_date as date) =>

let

Source = A2_CostAdditives_Pack,
#"Filtered Rows" = Table.SelectRows(Source, each ([ex_plant] = source_plant) and ([Packaging] = packaging) and ([Additive] = additive)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [date_from] <= pricing_date),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"date_from", Order.Descending}}),
price = #"Sorted Rows"{0}[#"Price (USD/MT)"]

in
price

the challenge is that [Additive] is null in most cases. When there is no match between [Additive] and additve (= [Product Name_OW]) I want the function to take the null entry from the table below (A2_CostAdditives_Pack).

so something in the sense of: if no match between [Additive] and additive, then take [Additive] is null.

the question is: how to solve this? unfortunately I have not been able to do it myself after various attempts.

1. Is there an easy way to handle the null issue here without the need to write an if statement?

2 or do I need to write an if statement in the custom function? I tried to do this, but apparently I am not able to make it work.

Thanks so much for your kind contribution.

Cheers, Jan-Martin

[Vijay_A_Verma]

try replacing ([Additive] = additive) in your Filtered Rows step with

([Additive] = if [Additive] <> additive then null else additive)