The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |