logo标签列表

PowerQuery表格连接重复列名

powerbipowerquery
3

我在使用Power Query（M语言）的Table.Join()函数时遇到了问题。为了演示这个问题，我修改了从Table.Join文档页面中提取的示例。

请注意最后的JoinKind.LeftOuter提示。

= Table.Join(
    Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    }),
    "CustomerID",
    Table.FromRecords({
        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
        [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
        [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
        [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
        [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
    }),
    "CustomerID", JoinKind.LeftOuter

)

没有这个提示，函数可以正常运行。有了这个提示，由于重复的列名，函数会失败。
Expression.Error: A join operation cannot result in a table with duplicate column names ("CustomerID").
Details:
    [Type]

从我的角度来看，这是一个bug，关键字不能重复。

我遇到了这个问题，因为我想要连接的两个表除了连接键之外共享相同的列名。是否有技巧可以类似于SQL给它们命名别名？

3个回答
1

变量 1

有没有什么技巧可以将它们类似于 SQL 别名

是的，有，Table.PrefixColumns：

Table.Join(
    Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    }),
    "CustomerID",
    Table.PrefixColumns(Table.FromRecords({
        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
        [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
        [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
        [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
        [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
    }), "Orders"),
    "Orders.CustomerID", JoinKind.LeftOuter
)

变体2

@Alexis Olson的答案的改进版本。

即使内部连接仅允许键具有相同的列名，所有其他列都必须具有唯一的名称。 如果您的数据架构不稳定，您可能会发现无论连接类型如何，错误都会在这里和那里发生。 Alexis提出的嵌套连接很好。但是，您仍然需要列出列名。这是+1个需要更改的地方。

如果您手动编码（不使用GUI），则有更好的方法-将表转换为记录列表：

let
    Customers =
        Table.FromRecords({
            [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
            [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
            [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
            [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
        }),
    Orders =
        Table.FromRecords({
            [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
            [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
            [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
            [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
            [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
            [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
            [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
        }),
    LeftJoin =
        Table.NestedJoin(
            Customers, "CustomerID",
            Orders, "CustomerID",
            "Orders", JoinKind.LeftOuter
        ),
    TransformToList =
        Table.TransformColumns(
            LeftJoin,
            { { "Orders", each Table.ToRecords(_) } }
        ),
    #"Expanded Orders" =
        Table.ExpandListColumn(
            TransformToList, "Orders"
        )
in
    #"Expanded Orders"

所有字段仍然可访问，例如：

        Table.AddColumn(
            #"Expanded Orders",
            "Something for fishing?",
            each [Orders] <> null and Text.Contains([Orders][Item], "fish", Comparer.OrdinalIgnoreCase),
            type logical
        )

当然，这样嵌套的记录字段不能用作键或另一个连接。而且它们在 GUI 中也不能直接观察到。
1

如果是内连接，这个操作很好用，但是在左外连接中，这会导致列名重复，因为第一个表中的CustomerID和第二个表中的CustomerID是不同的。你可以通过重命名其中一个列来解决这个问题。例如，以下方法可行：

let
    Table1 = Table.Join(
    Table.FromRecords({
        [ID = 1, Name = "Bob", Phone = "123-4567"],
        [ID = 2, Name = "Jim", Phone = "987-6543"],
        [ID = 3, Name = "Paul", Phone = "543-7890"],
        [ID = 4, Name = "Ringo", Phone = "232-1550"]
    }),
    "ID",
    Table.FromRecords({
        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
        [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
        [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
        [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
        [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
    }),
    "CustomerID", JoinKind.LeftOuter

)
in
    Table1

如果您不想执行链接中提到的重命名解决方法，可以使用Table.NestedJoin和Table.Expand，就像GUI会生成的那样。
let
    Customers =
        Table.FromRecords({
            [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
            [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
            [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
            [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
        }),
    Orders =
        Table.FromRecords({
            [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
            [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
            [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
            [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
            [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
            [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
            [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
        }),
    LeftJoin =
        Table.NestedJoin
            Customers, "CustomerID",
            Orders, "CustomerID",
            "Orders", JoinKind.LeftOuter
        ),
    #"Expanded Orders" =
        Table.ExpandTableColumn(
            LeftJoin, "Orders",
            {"OrderID", "Item", "Price"}, {"OrderID", "Item", "Price"}
        )
in
    #"Expanded Orders"
我正在阅读您发布的链接，看起来他遇到了相同的问题。好的，所以这不是一个错误，只是不一致的行为。 - Jan
0

这对我来说似乎很好用 主要区别似乎是使用了NestedJoin而不是Table.Join

= Table.NestedJoin(Table.FromRecords({
    [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
    [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}),
{"CustomerID"},
Table.FromRecords({
    [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
    [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
    [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
    [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
    [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
    [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
}),
{"CustomerID"},"xx",JoinKind.LeftOuter)
NestedJoin函数是另一个类似的函数，我遇到的问题是Join函数。 - Jan
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接