根据值对 Power Query 进行筛选

我有两个来源 - 一个主列表（来源 1）和一个每周更新（来源 2）。我想对照来源 2 中的 ID 号进行检查

1. 过滤掉 Source1 中没有的 ID，并且

2. 检查 Source2 中的新 ID 在“状态”列中不包含字符串“已关闭”

我希望主列表基本上包含每周更新列表中添加的所有新 ID，并确保不包含任何已关闭的 ID。

let
  // Merge Source1 and Source2 (use a Full Outer Join to get all rows from both tables)
  MergedTables = Table.NestedJoin(Source1, {"ID"}, Source2, {"ID"}, JoinKind.FullOuter),

  // Expand Source2 columns
  ExpandedSource2Data = Table.ExpandTableColumn(#"MergedTables", 
    {{"Title",each Text.From(_)}, {"Problem Description",each Text.From(_)}, {"Originator",each Text.From(_)}, {"WOA/Event/Dev Number",each Text.From(_)}, {"Status", each Text.From(_)}, {"Impacted Subsystems", each Text.From(_)}, {"Date Entered", each Text.From(_)}, {"Date Closed", each Text.From(_)}, {"Problem Date",each Text.From(_)}, {"Disposition to Close", each Text.From(_)}, {"Root Cause of the Problem",each Text.From(_)}, {"Final Disposition", each Text.From(_)}}, 
    {{"Source2_Title",each Text.From(_)}, {"Source2_Problem Description",each Text.From(_)}, {"Source2_Originator",each Text.From(_)}, {"Source2_WOA/Event/Dev Number",each Text.From(_)}, {"Source2_Status",each Text.From(_)}, {"Source2_Impacted Subsystems",each Text.From(_)}, {"Source2_Date Entered",each Text.From(_)}, {"Source2_Date Closed",each Text.From(_)}, {"Source2_Problem Date",each Text.From(_)}, {"Source2_Disposition to Close",each Text.From(_)}, {"Source2_Root Cause of the Problem",each Text.From(_)}, {"Source2_Final Disposition", each Text.From(_)}}), 

  // Filter rows where ID is in Source2 but not in Source1 and Status in Source2 is not 'Closed'
  FilteredRows = Table.SelectRows(#"ExpandedSource2Data", each ([ID] <> null and [Source2][ID] <> null and [Source2][Status] <> "PR Closed")),

  // Combine Source1 and FilteredRows
  CombinedTables = Table.Combine({Source1, FilteredRows}),
  #"Removed columns" = Table.RemoveColumns(CombinedTables, {"Source2_Title", "Source2_Problem Description", "Source2_Originator", "Source2_WOA/Event/Dev Number", "Source2_Status", "Source2_Impacted Subsystems", "Source2_Date Entered", "Source2_Date Closed", "Source2_Problem Date", "Source2_Disposition to Close", "Source2_Root Cause of the Problem", "Source2_Final Disposition"})

in
  #"Removed columns"

我添加了，each Text.From(_)因为它一直给我一个错误指出：[Expression.Error] 我们无法将值 3 转换为文本类型......尽管它仍然没有解决。

示例数据：Source1/主列表：

| ID  | Title    | Description | Conclusion    | Status              |
|-----|----------|-------------|---------------|---------------------|
| 120 | Title120 | Desc120     | Conclusion120 | 120 Closed          |
| 137 | Title137 | Desc137     | Conclusion137 | 137 Open            |
| 142 | Title142 | Desc142     | Conclusion142 | 142 Fields Complete |

来源2/每周更新列表：

| ID  | Title    | Description | Conclusion    | Status              |
|-----|----------|-------------|---------------|---------------------|
| 120 | Title120 | Desc120     | Conclusion120 | 120 Closed          |
| 137 | Title137 | Desc137     | Conclusion137 | 137 Open            |
| 142 | Title142 | Desc142     | Conclusion142 | 142 Fields Complete |
| 001 | Title001 | Desc001     | Conclusion001 | 001 Closed          |
| 006 | Title006 | Desc006     | Conclusion006 | 006 ID Closed       |
| 600 | Title600 | Desc600     | Conclusion600 | 600 NEW             |
| 700 | Title700 | Desc700     | Conclusion700 | 700 Open            |

期望合并的输出只有添加到主列表中的新 ID，但不包括之前关闭的且不在主列表中的任何内容：

| ID  | Title    | Description | Conclusion    | Status              |
|-----|----------|-------------|---------------|---------------------|
| 120 | Title120 | Desc120     | Conclusion120 | 120 Closed          |
| 137 | Title137 | Desc137     | Conclusion137 | 137 Open            |
| 142 | Title142 | Desc142     | Conclusion142 | 142 Fields Complete |
| 600 | Title600 | Desc600     | Conclusion600 | 600 NEW             |
| 700 | Title700 | Desc700     | Conclusion700 | 700 Open            |