excel - 将表格与 Excel Power Query 合并

Question

我希望在电源查询中实现这一点。我有大约 50 张桌子要组合。我想基于列键合并表。问题是某些表包含重复键（在这种情况下为电子邮件），但 Table1 缺少 Table2 中的值，反之亦然。此外，表 2 可能缺少某些列或有附加列。也欢迎其他建议。也许是VBA？

例如：

表格1：

Email   |   First   | Last   | Phone |
123@mail  John                555-555
234@mail  Timothy     Smith   123-456
456@mail  Jim                 123-456

表2：

Email   |   First   | Last   | Phone | Location
123@mail               Doe             Toronto
234@mail  Timothy     Smith   123-456  Denver
456@mail  Jim         Barley  123-456  Cleveland

结果：

Email   |   First   | Last   | Phone | Location
123@mail  John        Doe     555-555  Toronto
234@mail  Timothy     Smith   123-456  Denver
456@mail  Jim         Barley  123-456  Cleveland

标签： excelpowerquery

Answer 1

使用 Power Query，您可以执行 NestedJoin 或组合操作。无论哪种方式，您都必须从相应的表中提取缺失的条目。

在下面的示例中，

• Table.Combine
  • 请注意，Table.Combine 参数是一个表列表，因此您可以组合两个以上。
  • 检查第 2-5 行，了解我如何从工作簿访问表格。
  • 第 2-3 行由 Power Query UI 生成。
  • 第 4-5 行是我手动输入的，以显示指定列数据类型的不同方法。
  • Table_2如果您要为同一封电子邮件聚合不同的条目，将数据类型指定为每列的文本可能很重要，就像我对 所做的那样。
• Group经过email
• 使用 List.Accumulate 的自定义聚合来提取非空条目。
  • 请注意，如果来自不同表的同一列中的项目不相同，您可以修改聚合公式以连接项目。

M代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    tbl1 = Table.TransformColumnTypes(Source,{{"Email", type text}, {"First", type text}, {"Last", type text}, {"Phone", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Table_2"]}[Content],
    tbl2 = Table.TransformColumnTypes(Source2,List.Zip({Table.ColumnNames(Source2), List.Repeat({type text}, List.Count(Table.ColumnNames(Source2)))})),
    comb = Table.Combine({tbl1,tbl2}),
    #"Grouped Rows" = Table.Group(comb, {"Email"}, {

    //Below are the custom formulas for each column
            {"First", each List.Accumulate(_[First],"",(state, current) => if state = "" and current <> null then current else state), type text},
            {"Last", each List.Accumulate(_[Last],"",(state, current) => if state = "" and current <> null then current else state), type text},
            {"Phone", each List.Accumulate(_[Phone],"",(state, current) => if state = "" and current <> null then current else state), type text},
            {"Location", each List.Accumulate(_[Location],"",(state, current) => if state = "" and current <> null then current else state), type text}
    })
in
    #"Grouped Rows"

聚合函数

• 这将返回组合列表中指定列的第一个非空条目

each List.Accumulate(_[column_header],"",(state, current) => if state = "" and current <> null then current else state)

• （each和后续）指的是函数_返回的分组项的子表。Table.Group

• 虽然不在这个论坛，但是这里有很好的List Accumulate 解释