Unable to get desired rows from parameterized function

[Asheesh]

Dear All, 

I am trying to return a row/ column from a parameterized function. Having below two data models -

Employee
Employee Id	Employee Name	Salary
1001	SAM	90,00,000.00
1002	John	70,00,000.00
1003	Rodrix	50,00,000.00

SalaryGrades
Grade ID	Min Sal	Max Sal
1	75,00,001.00	99,00,000.00
2	60,00,001.00	75,00,000.00
3	45,00,001.00	60,00,000.00

Below is the function which I created and using to add a new column -

//Function name GetSalGrade(InputSal as number)
= (InputSal as number) =>
let
#"SingleRowSal" = Table.SelectRows(#"SalaryGrades",each [Min Sal]>=InputSal and [Max Sal]<=InputSal),
Source = #"SingleRowSal"
in
Source

I am passing the values as 76,00,000 OR 65,00,000 OR 50,00,000 and each time it is returning a blank table.

My desired data model should be like below -

EmployeeSalaryGrade
Employee Id	Employee Name	Salary	Employee Garde
1001	SAM	90,00,000.00	1
1002	John	70,00,000.00	2
1003	Rodrix	50,00,000.00	3

(Actually I will add a new column EmployeeGrade in the existing Employee Model)

Please suggest what wrong I am doing.

[Asheesh]

Thanks @AlB for your swift response. It's working 😀

[AlB]

@Asheesh 

See it all at work in the attached file

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @Asheesh 

Small change to your function:

(InputSal as number) =>
let
SingleRowSal = Table.SelectRows(#"SalaryGrades",each [Min Sal]<=InputSal and [Max Sal]>=InputSal),
Source = try SingleRowSal[Grade ID]{0} otherwise null
in
Source

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.