如何使用Power Query去除单元格内文本字符串的重复值

问题原因 #

分组聚合阶段直接对原始地址列执行Text.Combine时，未提前过滤同组内重复的邮箱地址，导致合并后单个单元格内出现重复值。

最优实现方式 #

不建议先合并成文本再拆分去重，直接在分组聚合逻辑中用List.Distinct()对分组内的地址列表先去重，再执行文本合并，逻辑最简、性能最高。

修改位置 #

原代码第一次分组步骤#"Grouped Rows"中的聚合逻辑，原写法直接传入原始列做合并：

{"cc_address", each Text.Combine([CcRecipients.Address],", "), type text}, 
{"to_address", each Text.Combine([ToRecipients.Address],", "), type text}

给待合并的列表套一层List.Distinct()即可完成去重，修改后：

{"cc_address", each Text.Combine(List.Distinct([CcRecipients.Address]),", "), type text}, 
{"to_address", each Text.Combine(List.Distinct([ToRecipients.Address]),", "), type text}

修改后完整M代码 #

let
    Source = Exchange.Contents("giang.phan@abc.com"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Reordered Columns" = Table.ReorderColumns(Mail1,{"DateTimeSent", "DateTimeReceived", "Folder Path", "Subject", "Sender", "DisplayTo", "DisplayCc", "ToRecipients", "CcRecipients", "BccRecipients", "Importance", "Categories", "IsRead", "HasAttachments", "Attachments", "Preview", "Attributes", "Body", "Id"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [DateTimeReceived] > #datetime(2021, 12, 29, 0, 0, 0) and [DateTimeReceived] < #datetime(2022, 1, 4, 0, 0, 0)),
    #"Expanded ToRecipients" = Table.ExpandTableColumn(#"Filtered Rows", "ToRecipients", {"Address"}, {"ToRecipients.Address"}),
    #"Expanded CcRecipients" = Table.ExpandTableColumn(#"Expanded ToRecipients", "CcRecipients", {"Address"}, {"CcRecipients.Address"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded CcRecipients",{"BccRecipients", "Importance", "Categories", "IsRead", "HasAttachments", "Attachments", "Preview", "Attributes"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Id", "DateTimeSent", "DateTimeReceived", "Folder Path", "Subject", "Sender", "DisplayTo", "DisplayCc", "ToRecipients.Address", "CcRecipients.Address", "Body"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns1", {"Id", "DateTimeSent", "DateTimeReceived", "Folder Path", "Subject", "DisplayTo", "DisplayCc"}, {
        {"cc_address", each Text.Combine(List.Distinct([CcRecipients.Address]),", "), type text}, 
        {"to_address", each Text.Combine(List.Distinct([ToRecipients.Address]),", "), type text}
    }),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Id", "DateTimeSent", "DateTimeReceived", "Folder Path", "DisplayTo", "DisplayCc", "cc_address", "to_address"}, {{"Last_time receive", each List.Max([DateTimeReceived]), type datetime}, {"Last_subject", each List.Max([Subject]), type nullable text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows1",{"Id", "DateTimeSent", "DateTimeReceived", "Folder Path", "DisplayTo", "DisplayCc", "cc_address", "to_address", "Last_time receive", "Last_subject"})
in
    #"Removed Other Columns"

事后补救方案 #

如果不想修改原有分组逻辑，也可以在分组步骤完成后新增自定义列，对已经合并完成的文本执行「按分隔符拆分-列表去重-重新合并」操作，以cc_address列为例，自定义列公式如下：

Text.Combine(List.Distinct(Splitter.SplitTextByDelimiter(", ")([cc_address])),", ")

to_address列替换对应列名即可。

注意：该方案需要额外执行文本拆分操作，数据量较大时性能弱于分组阶段直接去重的方案，优先选择前者。

内容的提问来源于stack exchange，提问作者thangvc91