Forum Discussion
Solved
Unable to get desired rows from parameterized function
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.
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 SourcePlease 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.
3 Replies
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 SourcePlease 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.
