Hello, I am new to M query language, however I have need to covert a fairly complex excel formula into M. I’m not looking to have the formula written for me, but if there are any tips, pointers, or similar examples out there that would help me get to started, I would really appreciate it. Are there any general strategies for how to convert a formula from Excel into M?
The formula is designed to calculate an individual’s retirement eligibility date using a set of encoded business rules and 4 variables: Occupational Series Number, Birth Date, Employee Start Date, and Retirement Plan Code. The formula uses the excel functions of MIN, MAX, YEAR, VLOOKUP, and nested IF statements that go several layers deep. Below is the formula for reference. Thanks so much for any assistance.
=IF(OCCUPATIONAL SERIES NUMBER=1811,IF(IF((EMPLOYEE START DATE+365.25*25)<=(BIRTH DATE+365.25*50),(EMPLOYEE START DATE+365.25*25),IF((BIRTH DATE+365.25*50)>=(EMPLOYEE START DATE+365.25*20),(BIRTH DATE+365.25*50),(EMPLOYEE START DATE+365.25*20)))<=(BIRTH DATE+365.25*57),IF((EMPLOYEE START DATE+365.25*25)<=(BIRTH DATE+365.25*50),(EMPLOYEE START DATE+365.25*25),IF((BIRTH DATE+365.25*50)>=(EMPLOYEE START DATE+365.25*20),(BIRTH DATE+365.25*50),(EMPLOYEE START DATE+365.25*20))),(BIRTH DATE+365.25*57)),IF(RETIREMENT PLAN CODE="CSRS",MIN(MAX(BIRTH DATE+55*365.25,EMPLOYEE START DATE+30*365.25),MAX(BIRTH DATE+60*365.25,EMPLOYEE START DATE+20*365.25),MAX(BIRTH DATE+62*365.25,EMPLOYEE START DATE+5*365.25)),IF(RETIREMENT PLAN CODE="FERS",MIN(MAX(BIRTH DATE+VLOOKUP(YEAR(BIRTH DATE),{1920,55;1921,55},2,FALSE)*365.25,EMPLOYEE START DATE+30*365.25),MAX(BIRTH DATE+60*365.25,EMPLOYEE START DATE+20*365.25),MAX(BIRTH DATE+62*365.25,EMPLOYEE START DATE+5*365.25)),"Unavailable")))
