PDF转Excel表后Power Query条件去重（排除Client:行）的实现咨询

当前场景：将PDF转成Excel表格后，用Power Query清理数据时，执行Table.Distinct会误删Column1为"Client:"的行，需要保留这类行，仅对其他行按指定列去重。

实现思路 #

把表格拆成两部分处理：

• 第一部分：筛选出Column1等于"Client:"的所有行，直接保留
• 第二部分：筛选出其余行，对这部分执行指定列的去重
• 最后将两部分表格合并，得到最终结果

修改后的M语言代码 #

let
    Fonte = Pdf.Tables(File.Contents("S:\\Aplicativo\\API Relatório\\RelSC18102023_085032456.pdf"), [Implementation="1.3"]),
    #"Data Expandido" = Table.ExpandTableColumn(Fonte, "Data", {"Column1", "Column2", "Column3","Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"},{"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8","Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13"}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Data Expandido",{"Id", "Name", "Kind"}),
    #"Remover Nulls" = Table.FromColumns(List.Zip(List.RemoveNulls(List.Transform(Table.ToRows(#"Colunas Removidas"), each if List.NonNullCount(_)=0 then null else List.RemoveNulls(_))))),
    // 拆分表格：保留Column1为"Client:"的行
    #"Linhas Clientes" = Table.SelectRows(#"Remover Nulls", each [Column1] = "Client:"),
    // 筛选出需要去重的其他行
    #"Linhas Para Remover Duplicatas" = Table.SelectRows(#"Remover Nulls", each [Column1] <> "Client:"),
    // 对非客户行执行指定列去重
    #"Duplicatas Removidas" = Table.Distinct(#"Linhas Para Remover Duplicatas", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    // 合并两部分表格
    #"Tabela Final" = Table.Combine({#"Linhas Clientes", #"Duplicatas Removidas"})
in
    #"Tabela Final"

关键步骤说明 #

• 拆分表格：用Table.SelectRows分别筛选出两类行，确保"Client:"行不会进入去重流程
• 定向去重：仅对非"Client:"的行执行Table.Distinct，指定需要判断重复的列
• 合并表格：用Table.Combine把保留的行和去重后的行合并，保证数据完整

内容的提问来源于stack exchange，提问作者João Paulo Francisconi