Forum Discussion

saxenaa's avatar
saxenaa
Frequent Visitor
9 years ago
Solved

Create a new table using columns from other tables

I am sure this is trvial , but I am not sure how to do this.,   I have table A,B,C,D and I need to create new table Z with selected columns from A,B,C,D   I tried SELECTCOLUMNS but it selects col...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi saxenaa,

    I create sample table and reproduce your scenario. You can get expected result by adding index column in A,B,C,D then create Z including index column, to get all select columns using the relation between index. Please review more details as follows.

    I create TableA nad Table, I add index column by click Index Column under Add column. Please see TableA\B with index columns.

    Add index column in Query Editor
    TableATableB

    2. Get the max index value of A and B, there are 7>4 rows, so I select index in B to create TableZ. Then create relationship between A and Z, B and Z as follows.



    3. If I want to select Column1 and Column2 from A, and Column1 from B to create Z, I just create the calculated columns using the formulas in Table Z.

    A.Column1 = RELATED(A[Column1])
A.Column2 = RELATED(A[Column2])
B.Column1 = RELATED(B[Column1])


    You will get the expected result:

    TableZ

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi saxenaa,

I create sample table and reproduce your scenario. You can get expected result by adding index column in A,B,C,D then create Z including index column, to get all select columns using the relation between index. Please review more details as follows.

I create TableA nad Table, I add index column by click Index Column under Add column. Please see TableA\B with index columns.

Add index column in Query Editor
TableATableB

2. Get the max index value of A and B, there are 7>4 rows, so I select index in B to create TableZ. Then create relationship between A and Z, B and Z as follows.



3. If I want to select Column1 and Column2 from A, and Column1 from B to create Z, I just create the calculated columns using the formulas in Table Z.

A.Column1 = RELATED(A[Column1])
A.Column2 = RELATED(A[Column2])
B.Column1 = RELATED(B[Column1])


You will get the expected result:

TableZ

Best Regards,
Angelia