How to do merge join with two conditional fields?

[rlpa1295]

Hi, this is my question. I try to do a merge join with two tables that don't have just one field in common. Both tables have two fields in commom (the date and the ID User). But one of them has a date that I want to take in just one table. So I have problems to do the merge join because I have two fields and Power BI just allow me to do Merge with one field in common. In fact, I was wondering if I can do this SQL Query in Power BI.

Select * from table A inner join table B on (A.ID_User=B.Id_User AND A.DateReg=B.DateReg)

Thanks for your time

Rolando

[Eric_Zhang · Accepted solution]

---

@rlpa1295 wrote:

Hi, this is my question. I try to do a merge join with two tables that don't have just one field in common. Both tables have two fields in commom (the date and the ID User). But one of them has a date that I want to take in just one table. So I have problems to do the merge join because I have two fields and Power BI just allow me to do Merge with one field in common. In fact, I was wondering if I can do this SQL Query in Power BI.

Select * from table A inner join table B on (A.ID_User=B.Id_User AND A.DateReg=B.DateReg)

---

You can surely do that inner join in Power BI. Just press shift and check more columns. See the small sequence numbers 1,2 in the snapshot.

TableB

let
    Source = Table.FromRows({{"user1", "2016-01-01"}, 
{"user5","2016-01-01"},
{"user3", "2016-01-07"}, 
{"user6", "2016-07-01"}},
{"ID_User","DateReg" } 
),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateReg", type date}})

in 
	#"Changed Type"

TableA

let
    Source = Table.FromRows({{"user1", "2016-01-01"}, 
{"user2","2016-01-01"},
{"user3", "2016-01-07"}, 
{"user4", "2016-07-01"}},
{"ID_User","DateReg" } 
),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateReg", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ID_User", "DateReg"},TableB,{"ID_User", "DateReg"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ID_User", "DateReg"}, {"NewColumn.ID_User", "NewColumn.DateReg"})

in 
	#"Expanded NewColumn"

[Anonymous]

Hi @Eric_Zhang 
   Am also facing this type of problem. but i have million records. when i try to this way it take huge time only. can we implement to dax side?.