主页 / user-8565759

Brain_overflowed's questions

Martin Hope
Brain_overflowed
Asked: 2024-08-27 07:19:14 +0800 CST
  • 5

我有两个来源 - 一个主列表（来源 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            |
excel
Martin Hope
Brain_overflowed
Asked: 2024-08-27 00:20:42 +0800 CST
  • 5

我有一个简单的 XLOOKUP 公式，它在 master_list 表中查找一个字符串，如果该字符串存在于 weekly_update 表中，它应该使用 weekly_update 表中的内容更新 master_list 中选定列的内容。

目前，我使用 XLOOKUP 手动执行此操作，但由于它有很多列并且需要每隔几天执行一次，因此我尝试使用 PQ 来实现它。我目前拥有的合并查询代码（Source1 = master_list，Source2 = weekly_update）：

let
    // Merge Source1 and Source2
    MergedTables = Table.NestedJoin(Source1, {"ID Number"}, Source2, {"ID Number"}, "Source2Data", JoinKind.LeftOuter),

    // Expand Source2 columns
    ExpandedSource2Data = Table.ExpandTableColumn(MergedTables, "Source2Data", 
        {"Title", "Description", "Status", "Disposition"}, 
        {"Source2_Title", "Source2_Description", "Source2_Status", "Source2_Disposition"}), 

    // Replace columns from Source1 with columns from Source2 (Conditional logic with change check and null handling)
    UpdatedColumns = Table.TransformColumns(ExpandedSource2Data, {
        {"Title", each if [Source2Data] <> null and (try Text.From([Title]) <> [Source2Data][Title] otherwise false) then [Source2Data][Title] else [Title], type text},
        {"Description", each if [Source2Data] <> null and [Description] <> [Source2Data][Description] then [Source2Data][Description] else [Description], type text},
        {"Status", each if [Source2Data] <> null and [Status] <> [Source2Data][Status] then [Source2Data][Status] else [Status], type text},
        {"Disposition", each if [Source2Data] <> null and [Disposition] <> [Source2Data][Disposition] then [Source2Data][Disposition] else [Disposition], type text}
    }),

    // Remove temporary columns 
    FinalTable = Table.RemoveColumns(UpdatedColumns,
        {"Source2_Title", "Source2_Description", "Source2_Status", "Source2_Disposition"})

in
    if Table.RowCount(FinalTable) > 0 then FinalTable else null  // Return the entire table or null if empty

这总是返回合并的表而不更新列，我无法弄清楚我遗漏了什么。

附言：我对 Power Query 还很陌生，因此，对于任何明显的疏忽，请谅解。

excel-formula