How to join two tables based on a row of one and a column of the other?

[Anonymous]

Hello

I have the data:

Name; Index;

A           1

B           2

C           3

etc

I also have created an empty table and added calculated columns so that the following table is created:

       1;          2;         3;

Calc1;    Calc2;   Calc3

etc

How can I create the table:

Name; Index;  Calc;

A           1       Calc1

B           2        Calc2

C           3        Calc3

Thanks! By the way, if you have any better strategy to put calculations/measures in rows, please let me know! So that, I get the table above without creating two separate tables and trying to join them.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

1. Create a calculated table.

Table 3 = 
VAR X=
UNION(
    SELECTCOLUMNS( 'Table (2)',"C1", MAX('Table (2)'[Column1]), "C2", MIN('Table (2)'[Column1]) ),
    SELECTCOLUMNS( 'Table (2)', "C1", MAX('Table (2)'[Column2]), "C2", MIN('Table (2)'[Column2]) ),
    SELECTCOLUMNS( 'Table (2)', "C1", MAX('Table (2)'[Column3]), "C3", MIN('Table (2)'[Column3]) )
)
RETURN
DISTINCT(
    X
)

2. Create a calculated column.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You should unpivot or transpose your second table, and then you can merge the two on Index to create your desired output.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Unfortunately this does not work. It is not possible to transpose the table because the added calculated columns do not appear in the Table under Edit Queries.

Any other solution?

[Anonymous]

The issue you're facing is one more reason why one should try NOT to use calculated columns. There are many more reasons, of course.

If you can add/create the columns in Power Query (and it's hardly imaginable you couldn't), you'll have no problems doing what you want and you'll get the benefits of correct compression, and hence faster DAX.

Best
D