Help on a very specific and interesting join of 3 tables

[josedagadea]

How can I make this join efficiently?

I have 3 tables. For the sake of simplicity, assume each column in this table is a separate table, and the rows below are the values I want to join in each table:

Table A	Table B	Table C
a	d	f
f	a	c
a	f	f

As you can see, there are some repeating values as seen in table A, the value "a". I want to make a join in a way that the matching values appear in the same row for all of the 3 tables, AND for the values that do NOT match, have a null resulting value in the other columns like:

Table A	Table B	Table C
a	a	null
a	null	null
null	null	c
null	d	null
f	f	f

Note that the repeating value in table A is taken into account as another row and compared again with the other tables.

Any help would be great. I haven't found an efficient way to do this.

[v-nmadadi-msft]

Hi @josedagadea ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[AlienSx]

with your simplified table I would go this way

let
    Source = #table({"Table A", "Table B", "Table C"}, {{"a", "d", "f"}, {"f", "a", "c"}, {"a", "f", "f"}}),
    cols = List.Buffer(Table.ColumnNames(Source)),
    values = List.Distinct(List.Combine(Table.ToList(Source, (x) => x))),
    tx = List.Transform(
        values,
        (x) => List.Zip(
            List.Transform(
                cols, 
                (w) => ((lst) => if lst = {} then {null} else lst)(List.Select(Table.Column(Source, w), (z) => z = x))
            )
        )
    ),
    result = Table.FromList(List.Combine(tx), (x) => x, cols)
in
    result