PowerQuery Table.Join duplicate column names

Question

I ran into a problem with Power Query (M Language) Table.Join() function. Just to demonstrate the issue, I am altering the example taken from the Table.Join Documentation Page.

See the JoinKind.LeftOuter hint at the end

= 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

)

Without this hint the the function is running well. With the hint it is failing because of the duplicate column name.

Expression.Error: A join operation cannot result in a table with duplicate column names ("CustomerID").
Details:
    [Type]

From my perspective this is a bug, the key cannot be a duplicate.

I run into this issue, because the two tables I am trying to join share the same column names besides the join key. Is there a trick how to alias them similarly to a SQL?

Answer 1

This works fine if it's an inner join but with a left outer join, this leads to duplicate column names because CustomerID from the first table and CustomerID from the second table are different. You could rename one of the columns as a workaround. For example, this does work:

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

If you don't want to do the renaming workaround mentioned in that link, you can use Table.NestedJoin and Table.Expand like the GUI would generate.

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"

Answer 2

Variant 1

Is there a trick how to alias them similarly to a SQL

Yes, there is, 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
)

Variant 2

Improved version of @Alexis Olson's answer.

Even inner join allows equal column names for keys only. All other columns have to have unique names. If your data schema is not stable you can find that errors happen here and there, regardless of join kind. Nested join, proposed by Alexis, is good. But you still have to list column names. That's +1 place to change.

If you code by hand (not using GUI much), there is better approach — convert tables to lists of records:

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"

All fields remain accessible, for example:

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

But of course such nested record fields can't be used for keys or another join. And they are not directly observable with GUI.

Answer 3

This seems to work fine for me Main differences seems to be NestedJoin instead of 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)