What Power Query for combinations between Excel tables?

Question

Although it's pretty straightforward in SQL, I can't get the Power Query M code required to get all combinations between association tables in Excel. Here are my (fictitious) tables and data:

Color:

ID	Name
#FF0000	Red
#00FF00	Green
#0000FF	Blue

question from:https://stackoverflow.com/questions/65834788/what-power-query-for-combinations-between-excel-tables

Answer 1

Generally, Power Query works on the idea of joining just two tables at a time so you need to take a series of steps.

Using just the GUI to merge and expand columns, you can get the final result with M code that looks like this:

let
    Source = ColorInObject,
    #"Merged Queries" = Table.NestedJoin(Source, {"ColorID"}, ColorInPaint, {"ColorID"}, "ColorInPaint", JoinKind.LeftOuter),
    #"Expanded ColorInPaint" = Table.ExpandTableColumn(#"Merged Queries", "ColorInPaint", {"PaintID"}, {"PaintID"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded ColorInPaint", {"ObjectID"}, Object, {"ID"}, "Object", JoinKind.LeftOuter),
    #"Expanded Object" = Table.ExpandTableColumn(#"Merged Queries1", "Object", {"Name"}, {"Object.Name"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded Object", {"ColorID"}, Color, {"ID"}, "Color", JoinKind.LeftOuter),
    #"Expanded Color" = Table.ExpandTableColumn(#"Merged Queries2", "Color", {"Name"}, {"Color.Name"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded Color", {"PaintID"}, Paint, {"ID"}, "Paint", JoinKind.LeftOuter),
    #"Expanded Paint" = Table.ExpandTableColumn(#"Merged Queries3", "Paint", {"Name"}, {"Paint.Name"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Paint",{"Object.Name", "Color.Name", "Paint.Name"})
in
    #"Removed Other Columns"

You can cut down on some of the verbosity if you tinker with the M by hand:

let
    JoinTables =
        Table.NestedJoin(
            Table.NestedJoin(
                Table.NestedJoin(
                    Table.Join(
                        ColorInPaint, "ColorID",
                        ColorInObject, "ColorID"
                    ), "ObjectID",
                    Object, "ID", "Object"
                ), "PaintID",
                Paint, "ID", "Paint"
            ), "ColorID",
            Color, "ID", "Color"
        ),
    Expand =
        Table.ExpandTableColumn(
            Table.ExpandTableColumn(
                Table.ExpandTableColumn(
                    JoinTables, "Object", {"Name"}, {"Object.Name"}
                ), "Paint", {"Name"}, {"Paint.Name"}
            ), "Color", {"Name"}, {"Color.Name"}
        )
in
    Table.SelectColumns(Expand,{"Object.Name", "Paint.Name", "Color.Name"})