先看样例和预期结果

假设两份订单表来自不同同事。一份写「订单号」,另一份写「订单编号」。同一个订单更新了金额,就用较晚的版本。如果两条最新记录的时间完全一样、金额却不同,就放进异常表人工核对。

练习工作簿包含两张已命名的 Excel 表格 OrdersA 和 OrdersB,共 10 条虚构订单。

下载 Excel 样例

例如订单 0007 在两份表里各有一条。合并后,应该保留 11 月 19 日更新的 135 元,订单号前面的零也应保留。

订单号更新时间金额(元)来源
000711-19 15:00135来源 B
000811-18 10:2075来源 A
000911-19 10:00320来源 B
001111-18 13:00100来源 A

在 Excel 里创建查询

  1. 用 Windows 桌面版 Excel 打开样例。依次选择数据 → 获取数据 → 自其他源 → 空白查询,进入 Power Query 编辑器。
  2. 把查询命名为 订单整理。打开高级编辑器,用下面的代码替换原有内容,然后点击完成。
  3. 结果会显示一个包含「总表」「异常」「重复」的记录。分别新建三个空白查询,在公式栏输入 = 订单整理[总表]、= 订单整理[异常] 或 = 订单整理[重复],再使用关闭并加载把各个结果放到工作表中。公式栏可在视图中打开。
展开完整 Power Query 代码
let
    工作簿 = Excel.CurrentWorkbook(),
    字段 = {"订单号", "更新时间", "金额", "来源"},
    整理列 = (输入 as table, 来源 as text) as table =>
        let
            改列名 = Table.RenameColumns(输入, {
                {"订单编号", "订单号"}, {"更新日期", "更新时间"}, {"订单金额", "金额"}
            }, MissingField.Ignore),
            取所需列 = Table.SelectColumns(改列名, List.FirstN(字段, 3), MissingField.UseNull),
            统一类型 = Table.TransformColumns(取所需列, {
                {"订单号", each try Text.Trim(Text.From(_)) otherwise null, type nullable text},
                {"更新时间", each try DateTime.From(_, "zh-CN") otherwise null, type nullable datetime},
                {"金额", each try Number.From(_, "zh-CN") otherwise null, type nullable number}
            })
        in
            Table.AddColumn(统一类型, "来源", each 来源, type text),
    合并 = Table.Combine({
        整理列(工作簿{[Name="OrdersA"]}[Content], "来源 A"),
        整理列(工作簿{[Name="OrdersB"]}[Content], "来源 B")
    }),
    核对 = Table.AddColumn(合并, "待核查", each
        if [订单号] = null or [订单号] = "" then "缺订单号"
        else if [更新时间] = null then "缺更新时间或无法识别"
        else if [金额] = null then "缺金额或无法识别"
        else null, type nullable text),
    缺字段 = Table.SelectRows(核对, each [待核查] <> null),
    有效行 = Table.RemoveColumns(Table.SelectRows(核对, each [待核查] = null), {"待核查"}),
    按订单分组 = Table.Group(有效行, {"订单号"}, {{"版本", each _, type table}}),
    标记最新 = Table.AddColumn(按订单分组, "最新行", each
        let
            所有版本 = [版本],
            最晚时间 = List.Max(所有版本[更新时间])
        in
            Table.SelectRows(所有版本, each [更新时间] = 最晚时间), type table),
    标记冲突 = Table.AddColumn(标记最新, "有冲突", each
        List.Count(List.Distinct([最新行][金额])) > 1, type logical),
    冲突组 = Table.SelectRows(标记冲突, each [有冲突]),
    冲突行 = Table.AddColumn(Table.Combine(冲突组[最新行], 字段), "待核查",
        each "同一更新时间出现不同金额", type text),
    可入表 = Table.AddColumn(Table.SelectRows(标记冲突, each not [有冲突]), "记录", each
        Table.Sort([最新行], {{"来源", Order.Ascending}}){0}, type record),
    总表 = Table.Sort(Table.FromRecords(可入表[记录], 字段, MissingField.UseNull),
        {{"订单号", Order.Ascending}}),
    重复组 = Table.SelectRows(按订单分组, each Table.RowCount([版本]) > 1),
    重复 = Table.Combine(重复组[版本], 字段)
in
    [总表 = 总表, 异常 = Table.Combine({缺字段, 冲突行}), 重复 = 重复]

注意 Excel 的「追加」按列名对齐,因此代码先统一列名,再把两份表纵向拼接。保留哪个版本由每个订单组内的最大更新时间决定,直接点「删除重复项」无法保证保留最新一条。

用三张表核对结果

  • 总表应有 4 条。核对 0007 保留 135 元,0011 保留来源 A 的 100 元。最新版本可能来自任意一份表。
  • 异常应有 4 条。一条缺订单号,一条缺更新时间,0010 的两条最新记录金额不同,需要回到源表确认。
  • 重复应有 6 条。它保留了三个重复订单的全部有效版本,方便追查。部分记录也会出现在总表或异常表中。

装入实际数据之前,先确认订单号是否只在单个渠道内唯一。如果会重复,去重键还要包含渠道。另请把订单号保留为文本,已经被 Excel 作为数字丢掉的前导零,需要按原始数据或约定长度恢复。

下次怎么用

把新行加进两张 Excel 表格,再选择数据 → 全部刷新。当来源变多时,列名一致的文件可以用 Power Query 从文件夹读取。不同格式先各自规范列名,方便以后对照哪张表发生了变化。

如果请 AI 帮你改查询,先给它列名、虚构的几行输入和预期输出。尤其要用「同订单、同时间、金额不同」这种边界样例检查它有没有误删数据。

参考资料

返回文章列表