根据年份、半年一次、每季度创建一行

Question

​

​

我希望将表动态地展开如下：

​

基本上，我试图实现的是，我输入的表的每一行都根据开始(授予日期)和结束日期(归属结束日期)为归属任期类型添加了行。

Answer 1

我认为您的示例在日期方面有点不合适，但这适用于powerquery。使用我的示例数据，将整个代码加载到home...advanced编辑器中.看看它在做什么。可能会更改Table1以反映从excel加载的数据的名称.来自表/范围...xheaders

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Grant Date", type date}, {"Vesting tenure", type text}, {"Vesting Period", Int64.Type}, {"Vesting end date", type date}}),
#"Added Custom"= Table.AddColumn( #"Changed Type", "End Date", 
    each let
    begin=[Grant Date],
    end=[Vesting end date],
    vtype=[Vesting tenure]
    in
    if vtype="Annually" then List.Generate(() => Date.AddYears(begin,1), each _ <= end, each Date.AddYears(_,1) )
    else if vtype="Quarterly" then List.Generate(() => Date.AddQuarters(begin,1), each _ <= end, each Date.AddQuarters(_,1) )
    else if vtype="Half Yearly" then List.Generate(() => Date.AddMonths(begin,6), each _ <= end, each Date.AddMonths(_,6) )
    else null
    ),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "End Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"End Date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Start Date", each if [Vesting tenure]="Annually" then Date.AddYears([End Date],-1) else if [Vesting tenure]="Quarterly" then Date.AddQuarters([End Date],-1) else if [Vesting tenure]="Half Yearly" then Date.AddMonths([End Date],-6) else null, type date),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Grant Date", "Vesting tenure", "Vesting Period", "Vesting end date", "Start Date", "End Date"})
in  #"Reordered Columns" 

​

​

如果需要，可以在日期前后使用Date.EndOfQuarter()或Date.EndOfMonth()