Forum Discussion
Helper IIIMerge 2 tables with Power BI Desktop
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,
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,
5 Replies
- Greg_Deckler
Community Champion
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
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
JonathanJohnsGood 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-msftMicrosoft Employee
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,
