Merge 2 tables with Power BI Desktop

[JonathanJohns]

Good afternoon everyone,

I am trying to merge two tables with Power BI but I don't find the way to do. 

The tables are from 2 Excel files. They have some similar columns but not all of them and they don't have the same number of columns.

For example, one is like that :

Lot	Date	Ind1	Ind2	Ind3
1	20/11/2017	5	30	200
2	21/11/2017	6	36	250
3	22/11/2017	4	33	225

And the other is like this one :

Lot	Date	Ind1	Ind2	Ind3	Ind4
1	20/11/2017	5	30	50	OK
2	21/11/2017	6	36	55	OK
3	22/11/2017	4	33	60	OK
4	23/11/2017	5	30	55	OK
5	24/11/2017	5	35	60	NO

And I want to one table like this one :

Lot	Date	Ind1	Ind2	ind3	Ind4	ind5
1	20/11/2017	5	30	200	50	OK
2	21/11/2017	6	36	250	55	OK
3	22/11/2017	4	33	225	60	OK
4	23/11/2017	5	30		55	OK
5	24/11/2017	5	35		60	NO

It's an example, in my case, one table has 170 columns and the other one 160 so the tables are so big that I cannot do it like before for each columns.

I have tried to do it on Excel directly with a function like vlookup() but it didn't work so I am trying with Power BI Desktop. 

Do you have any ideas to do it please ?

Cheers,

[Anonymous]

Hi @JonathanJohns,

Perhaps you can add a calculated column like:

Weigth = RELATED(Table1[Weigth])

You add the calculated column to the table you want to have this column in.

Let me know if this worked for you.

Regards,

L.Meijdam

[JonathanJohns]

Good morning,

I have tried with the two methods but I didn't succeed it. I did it by hand on Excel finally.

Thank you for your answers.

[v-sihou-msft]

@JonathanJohns

In this scenario, you want to find the match Weight based multiple column values, so you should use LOOKUPVALUE() function. 

=
LOOKUPVALUE (
    Table2[Weight],
    Table2[Lot], Table1[Lot],
    Table2[Date], Table1[Date],
    Table2[pH], Table1[pH]
)

Regards,

[JonathanJohns · Accepted solution]

I used the function Add a new request in the Editor to do it. It is fast and it works properly ! 🙂

Thank you for your help.

Regards,

[Greg_Deckler]

Several ways to do this. One, there is a Merge option in the Query Editor over on the far right of the ribbon.

Also, there is a NATURALINNERJOIN option in DAX:

https://msdn.microsoft.com/en-us/library/dn802543.aspx