有条件删除重复列 （上次发过， 可能没有说清楚要求）

kenese

附件中A:H为数据源， 其中H列为辅助列， 辅助列中属于以下的删除重复列（保留时间是最新的那行数据）， 不属于以下的保留重复列， 不删除。
Unaccountable,Assembly,SCRAP
AssyCollectedLoss,Assembly,SCRAP
AssemblyQCLoss,Assembly,SCRAP
MoldingQC_Loss,Molding,SCRAP
RBI_Open,RBI,SCRAP





   

sm629

试试：

1. let
   
2.     源 = Table.TransformColumns(Excel.CurrentWorkbook(){[Name="源"]}[Content],{"日期时间",DateTime.From},Text.From),
   
3.     筛选需去重 = Table.SelectRows(源, each ([辅助列] = "Unaccountable,Assembly,SCRAP" or [辅助列] = "AssyCollectedLoss,Assembly,SCRAP" or [辅助列] = "AssemblyQCLoss,Assembly,SCRAP" or [辅助列] = "RBI_Open,RBI,SCRAP" or [辅助列] = "MoldingQC_Loss,Molding,SCRAP")),
   
4.     分组的行 = Table.Combine(Table.Group(筛选需去重, {"工单", "辅助列"}, {{"a", each Table.MaxN(_,"日期时间",1)}})[a]),
   
5.     筛选不去重 = Table.SelectRows(源, each ([辅助列] <> "Unaccountable,Assembly,SCRAP" and [辅助列] <> "AssyCollectedLoss,Assembly,SCRAP" and [辅助列] <> "AssemblyQCLoss,Assembly,SCRAP" and [辅助列] <> "RBI_Open,RBI,SCRAP" and [辅助列] <> "MoldingQC_Loss,Molding,SCRAP")),
   
6.     合并 = Table.Combine({分组的行, 筛选不去重}),
   
7.     排序的行 = Table.Sort(合并,{{"工单", Order.Ascending}, {"日期时间", Order.Ascending}})
   
8. in
   
9.     排序的行

复制代码

kenese

sm629 发表于 2024-9-7 22:19
试试：

把需要去重的晒出来按照时间顺序去重生成一个表， 把不需要去重的生成一个表， 然后再合并排序。  妙， 实在是高！

minchar

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组的行 = Table.Group(源, {"工单", "辅助列"}, {"所有内容", each _}),
    a = {"Unaccountable,Assembly,SCRAP","AssyCollectedLoss,Assembly,SCRAP","AssemblyQCLoss,Assembly,SCRAP","MoldingQC_Loss,Molding,SCRAP","RBI_Open,RBI,SCRAP"},
    遍历表 = Table.TransformColumns(分组的行,{"所有内容",each if List.MatchesAny(a,(x)=> x=[辅助列]{0}) then Table.LastN(_,1) else _ }),
    删除的列 = Table.RemoveColumns(遍历表,{"辅助列"}),
    #"展开的“所有内容”" = Table.ExpandTableColumn(删除的列, "所有内容", {"日期时间", "类型", "数量", "料号", "站点", "处置", "辅助列"}, {"日期时间", "类型", "数量", "料号", "站点", "处置", "辅助列"}),
    更改的类型 = Table.TransformColumnTypes(#"展开的“所有内容”",{{"日期时间", type datetime}})
in
    更改的类型

jiffy92010

分组，判断留末或留整，合并