Combine two formulas in power query-CodePudding

Need to generate the end date based on Vesting type column

I am able to generate formulas to create the end date as per the below column

I have been able to generate the formulas in power query in two separate tables for the same data using the codes below.

Graded vesting formula

let 
StDt = [#"Grant date #(lf)(dd/mm/yyyy)"],
end = [#"Vesting end date#(lf)(dd/mm/yyyy)"],
vType = [Vesting tenure]
in
if vType ="Annually" then List.Generate(() => Date.AddYears(StDt,1),each _ <= end,each Date.AddYears(_,1))
else if vType = "Quarterly" then List.Generate (() => Date.AddQuarters(StDt,1), each _ <=end, each Date.AddQuarters (_,1) )
else if vType="Half yearly" then List.Generate(() => Date.AddMonths (StDt,6), each _ <= end, each Date.AddMonths(_,6) )
else null

Bullet vesting

let 
Startdate= [#"Grant date #(lf)(dd/mm/yyyy)"],
Enddate = [End date],
Firstdate = Date.AddDays(Date.EndOfMonth(Startdate),1),
Lastdate = Date.StartOfMonth(Enddate),
ResultTemp = List.Generate(()=> [x=Firstdate], each [x] <= Lastdate, each[x=Date.AddMonths([x],1)], each [x]),
Result=List.Distinct({Startdate} & ResultTemp & {Enddate})
in
Result

I'm looking for a way to combine these two formulas in a single formula to update it in a single column.

The end objective is if it is graded vesting it should generate dates based on vesting tenure and if it is bullet vesting it should generate dates based on vesting end date column

CodePudding user response：

You could use below code. Change column names as needed to include the line feeds

let 
StDt = [Grant date],
end = [Vesting end date],
vType = [Vesting tenure],
vType2 = [Vesting type],
Enddate = [End date],
Firstdate = Date.AddDays(Date.EndOfMonth(StDt),1),
Lastdate = Date.StartOfMonth(Enddate)
in
if vType2 ="Bullet vesting" then List.Distinct({StDt} & List.Generate(()=> [x=Firstdate], each [x] <= Lastdate, each[x=Date.AddMonths([x],1)], each [x])& {Enddate})
else if vType ="Annually" then List.Generate(() => Date.AddYears(StDt,1),each _ <= end,each Date.AddYears(_,1))
else if vType = "Quarterly" then List.Generate (() => Date.AddQuarters(StDt,1), each _ <=end, each Date.AddQuarters (_,1) )
else if vType="Half yearly" then List.Generate(() => Date.AddMonths (StDt,6), each _ <= end, each Date.AddMonths(_,6) )
else null)