|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
连蒙带猜
- let
- Source = Excel.CurrentWorkbook(){[Name="数据"]}[Content],
- #"Changed Type" = Table.TransformColumnTypes(Source,{{"日期", type date}, {"代码", type text}, {"名称", type text}, {"时间", type time}}),
- Custom1 = Table.Group(#"Changed Type","名称",
- {"n",each Table.Group(Table.Sort(_,{"日期"}),"日期",
- {"最后日期",each List.Last([日期])},
- 0,
- (x,y)=>Number.From(y-x>#duration(1,0,0,0))
- )
- }
- ),
- xx=Table.TransformColumns(Custom1,{{"n",each [a=Table.RowCount(_),
- b=List.Distinct(#"Changed Type"[日期]),
- c=List.Transform({1..a-1},(x)=>List.PositionOf(b,[日期]{x})-List.PositionOf(b,[最后日期]{x-1})-1),
- d=Table.FromColumns({[日期],{0}&c},{"最早日期","间隔"})][d]
- }}),
- Custom2 = Table.ExpandTableColumn(xx,"n",Table.ColumnNames(xx[n]{0}))
- in
- Custom2
复制代码 |
|