cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saxenaa
Frequent Visitor

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 columns from one single table , I tried combining with CALCULATE but its did not worked.

any pointer would be great help.

I would like to do it using dax

 

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft
Microsoft

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 EditorAdd index column in Query Editor
TableATableATableBTableB

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.

6.PNG5.PNG

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:

TableZTableZ

Best Regards,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Microsoft
Microsoft

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 EditorAdd index column in Query Editor
TableATableATableBTableB

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.

6.PNG5.PNG

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:

TableZTableZ

Best Regards,
Angelia

View solution in original post

TomMartens
Super User
Super User

Hey,

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!