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 

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @josedagadea 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[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 · Accepted solution]

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