实现了,不知道有没有更好的办法
- let
- Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
- M = Table.TransformColumnTypes(Source,{{"报表时间", type date}}),
- A = Table.Group(M, {"报表时间"}, {{"Count", each _, type table [报表时间=datetime, 工号=text, 职位=text, 薪资=number]}}),
- B = Table.AddIndexColumn(A,"Index",0),
- #"Added Index" = Table.Skip(Table.AddColumn(B,"Pre_month",each if [Index]=0 then null else Table.SelectRows(B,(t)=> t[Index]+1=[Index] )[Count]{0}),1),
- #"Removed Columns" = Table.CombineColumns(
- Table.RemoveColumns(
- #"Added Index",
- {"Index"}),
- {"Count","Pre_month"},each
- Table.Group(
- Table.Combine(_),"工号",{
- {"A",(m)=> let
- a = Table.ToColumns(Table.RemoveColumns(m,{"工号"})){1},
- b = Table.ToColumns(Table.RemoveColumns(m,{"工号"})){2}
- in if List.IsDistinct(a) and List.IsDistinct(b)
- then "升职加薪" else
- if List.IsDistinct(a)
- then "升职" else
- if List.IsDistinct(b)
- then "加薪"
- else null
- }
- }
- ),"New"),
- #"Expanded New" = Table.ExpandTableColumn(#"Removed Columns", "New", {"工号", "A"}, {"工号", "A"}),
- #"Filtered Rows" = Table.SelectRows(#"Expanded New", each ([A] <> null)),
- #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"报表时间", "工号"}, M, {"报表时间", "工号"}, "Filtered Rows", JoinKind.LeftOuter),
- #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"职位", "薪资"}, {"职位", "薪资"})
- in
- #"Expanded Filtered Rows"
复制代码 |