Power Query更新Lead Data1指定列并追加Enrolment Data行报错解决

错误原因排查 #

你遇到的“Data Source not found”错误大概率是以下两种情况：

1. 文件路径未替换：代码中File.Contents("xxx.xlsb")是占位符，需替换为Lead Data所在文件的实际完整路径（比如C:\Documents\Leads.xlsb）。
2. Enrolment Data查询引用错误：确保单独创建的查询名称确实是#"Enrolment Data"，拼写、空格、大小写完全一致，Power Query对查询名称的匹配是严格的。

优化后的可行方案 #

你的核心思路是对的，但可以简化逻辑，避免不必要的列操作，同时确保数据处理的准确性：

完整代码 #

// 加载并处理Lead Data1
    Source = Excel.Workbook(File.Contents("你的实际文件路径.xlsb"), null, true),
    #"Lead Data1" = Source{[Name="Lead Data"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Lead Data1", [PromoteAllScalars=true]),
    #"Keep Required Columns" = Table.SelectColumns(#"Promoted Headers",{"Created On", "Owner", "Lead Number", "Source", "Source type", "TL", "Course", "Program", "Business"}),

    // 获取单独查询的Enrolment Data
    Enrolment = #"Enrolment Data",

    // 步骤1：从Lead Data1中筛选出未匹配Enrolment的行（无需更新的原数据）
    #"Lead Unmatched Rows" = Table.SelectRows(#"Keep Required Columns", each not List.Contains(Table.Column(Enrolment, "Lead Number"), [Lead Number])),

    // 步骤2：处理匹配的行——用Enrolment的数据更新Lead的指定列
    #"Lead Matched Rows" = Table.SelectRows(#"Keep Required Columns", each List.Contains(Table.Column(Enrolment, "Lead Number"), [Lead Number])),
    #"Merged Matched Rows" = Table.Join(#"Lead Matched Rows", {"Lead Number"}, Enrolment, {"Lead Number"}, JoinKind.Inner),
    #"Updated Matched Columns" = Table.FromRecords(
        Table.ToRecords(#"Merged Matched Rows"),
        (r) => [
            Created On = r[Enrolment.Created On],
            Owner = r[Owner],
            Lead Number = r[Lead Number],
            Source = r[Source],
            Source type = r[Source type],
            TL = r[TL],
            Course = r[Enrolment.Course],
            Program = r[Enrolment.Program],
            Business = r[Enrolment.Business]
        ]
    ),

    // 步骤3：处理Enrolment中存在但Lead没有的行（需要追加的新数据）
    #"Enrolment Unmatched Rows" = Table.SelectRows(Enrolment, each not List.Contains(Table.Column(#"Keep Required Columns", "Lead Number"), [Lead Number])),
    #"Add Missing Columns" = Table.AddColumn(#"Enrolment Unmatched Rows", "Owner", each null),
    #"Add Source Column" = Table.AddColumn(#"Add Missing Columns", "Source", each null),
    #"Add Source Type Column" = Table.AddColumn(#"Add Source Column", "Source type", each null),
    #"Add TL Column" = Table.AddColumn(#"Add Source Type Column", "TL", each null),
    #"Reorder Columns for Append" = Table.ReorderColumns(#"Add TL Column",{"Created On", "Owner", "Lead Number", "Source", "Source type", "TL", "Course", "Program", "Business"}),

    // 合并所有部分：更新后的匹配行 + 未更新的原行 + 新增的行
    #"Final Combined Data" = Table.Combine({#"Updated Matched Columns", #"Lead Unmatched Rows", #"Reorder Columns for Append"})
in
    #"Final Combined Data"

方案说明 #

• 拆分逻辑：将数据分为三部分处理，避免复杂的连接和行删除操作，更易排查问题：
  1. 无需更新的原Lead数据：筛选出Enrolment中没有的Lead Number行。
  2. 需更新的Lead数据：匹配Enrolment后，用Enrolment的列覆盖原Lead的对应列。
  3. 新增的Enrolment数据：补充Lead中缺失的列（设为null），确保列结构一致后追加。
• 性能优化：由于Enrolment数据量远小于Lead，优先基于Enrolment的Lead Number列表进行筛选，减少大表的计算量。

额外注意事项 #

• 如果Lead Data或Enrolment Data的列名有空格或特殊字符，确保代码中引用的列名完全一致。
• 若文件路径包含空格，需用双引号包裹完整路径，比如File.Contents("C:\My Documents\Leads.xlsb")。

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