Merge Query & Table with Duplications

[Gozde]

Hello,

I need to merge one query and one table in Power BI. The table has duplications and I would like to keep them because of the logic. At below, you can find the simple example of what I am trying to do.

Unfortunately, I cannot use power query or merge function, because one of them is query. I need to do in Power BI / dax.  I tried crossjoin, union,lookup but none of them solve the issue. Can anyone help me?

Thank you!

Gozde

[v-yuezhe-msft]

@Gozde,

Create the following columns in Table-2.

Lookupengineer = LOOKUPVALUE('Query-1'[Engineer],'Query-1'[Date],'Table-2'[Date])
Lookuplocation = LOOKUPVALUE('Query-1'[Location],'Query-1'[Date],'Table-2'[Date])



Regards,
Lydia

[Gozde · Accepted solution]

Lookup is not working for my case, because of dublications. I don't want to look up according to  engineer and location, because again dublication occurs. It can be that different engineer working in same location.

However, I found this solution:

I  changed the input table into this:

Afterwards, I look group 1 and 2 seperately via lookup function. It worked 🙂

Regards,

Gozde