Forum Discussion
Create a new table using columns from other tables
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
Super UserHey,
One solution could be to bind the columns just using CROSSJOIN (...) like so
Z Table =
CROSSJOIN(VALUES('A item'[A item]), CROSSJOIN(VALUES('B item'[B item]), VALUES('C item'[C item])))This creates a table with 3 columns and a number of rows = countrows('A item')*countrows('B item')*countrows('C item')
To be more specific how the new table will be composed you can use something like this
Z table =
GENERATEALL (
'A item',
VAR AitemBForeignKey = 'A item'[B ForeignKey]
RETURN
SELECTCOLUMNS (
CALCULATETABLE ( 'B item', 'B item'[B item ID] = AitemBForeignKey),
"acolumn", 'B item'[acolumn],
"bcolumn", 'B item'[bcolumn]
)
)Here an iterator is created, that iterates over table 'A item' stores a value from a column of the current row in the iterator into a variable and uses this variable to as a filter in the CALCULATETABLE() function.
Hope this gets you started