Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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,
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
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.
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,
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,
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