Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |