excel - 结合功率查询中的两个公式

Question

需要根据归属类型列生成结束日期

我能够根据下面的列生成公式来创建结束日期

我已经能够使用下面的代码在两个单独的表中为相同的数据生成电源查询中的公式。
分级归属公式

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

子弹归属

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

我正在寻找一种将这两个公式组合在一个公式中以在单个列中更新它的方法。
最终目标是，如果是分级归属，则应根据归属期限生成日期，如果是子弹归属，则应根据归属结束日期列生成日期

Answer 1

你可以使用下面的代码。根据需要更改列名以包含换行符

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)