Two columns from different table without any same key and create a new table

[Anonymous]

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.

[Anonymous · Accepted solution]

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. 

[parry2k]

@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/38490