Retrieve result when tables related by combination key (two columns)

[hksl]

I am a beginner. Power Bi Desktop free version. Creating table visualization.

I have following tables:

Table1: 

C1 , C2, C3       

1      2    a

1      3    g

2      2    c

2      3    h

Table2: 

C11, C22, C4    

1      2     91

1      2     45

1      3     73

2      2     12

2      3     87

2      3     21 

Need result as:

1     2    a    91+45

1     3    g    73

2     2    c     12

2     3    h     87+21

So far, I've created only result as: 

1     2         91+45

1     3        73

2     2         12

2     3         87+21

But I am having trouble to add values of C4 column values. 

My be to connect two tables on combination key that consists of 2 columns? Table1: C1,C2  and Table2: C11, C22 How to do that?  There is no many-to-many relationship in PBI.

Please help.

Many thanks

[v-haibl-msft]

@hksl

Do you want to get the sum result for the combinations in Table1 as below?

Best Regards,

Herbert

[hksl]

Yes I need that sum too. . .

[v-haibl-msft]

@hksl

To get the sum, have you tried to create a calculated column with the formula in my screenshot?

And do you want to get any other result except this result?

Best Regards,

Herbert

[BhaveshPatel]

You can do the Left or Right Join in the Query Editor by merging the Two Queries and then expanding the new column. 

Refer the screenshot for setting up the scenario.

MergeFinal Result

[hksl]

Also, Table2 has 3,502,458 rowa.

But I also need 'distinct' in result.

To simplify task:
I need result as
1     2    a   
1     3    g   
2     2    c    
2     3    h    

which is the same as to execute SQL query
select distinct t1.C4
from Table2 t2
join Table1 t1 on t2.C11 = t1.C1 and t2.C22 = t1.C2

I don't see any options available for using 'distinct' in 'Merge ....'

Thank you

[BhaveshPatel]

To get a distinct rows, You can do GROUPBY before merging the column.

See the screenshot.