Forum Discussion
Two columns from different table without any same key and create a new table
Hi,
I would like to create a new table and in that table, I would like to have two columns coming from two different tables without any similar key.
Such as:
TableA: 1,2,3,4
TableB: 5,8,9,19
New table: 3, 19
Any idea how to create it. I tried Summarize() but it is only giving one column.
Thanks in advance.
Hi Anonymous
If you want to merge two tables by dax or power query, there should be a same key column in them.
You can try to add index columns in Table A and TableB.
My Sample:
I add two Index columns in Power Query, then it will be easier for you to do some transform.
Merge in Power Query:
Select Merge as a new, merge TableA and TableB by Index columns, then expand 19 in TableB, remove other columns and rename TableB.19 as 19. Result is as below.
Dax:
Dax = SUMMARIZE(SUMMARIZE(TableA,TableA[Index],TableA[3],"19",CALCULATE(SUM(TableB[19]),FILTER(TableB,TableB[Index] = EARLIER([Index])))),[3],[19])Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous what is the logic to get these two values in the new table? Are these tables have relationship? Share sample data. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi Anonymous
If you want to merge two tables by dax or power query, there should be a same key column in them.
You can try to add index columns in Table A and TableB.
My Sample:
I add two Index columns in Power Query, then it will be easier for you to do some transform.
Merge in Power Query:
Select Merge as a new, merge TableA and TableB by Index columns, then expand 19 in TableB, remove other columns and rename TableB.19 as 19. Result is as below.
Dax:
Dax = SUMMARIZE(SUMMARIZE(TableA,TableA[Index],TableA[3],"19",CALCULATE(SUM(TableB[19]),FILTER(TableB,TableB[Index] = EARLIER([Index])))),[3],[19])Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
