Forum Discussion
How to combine 2 tables into 1
Hi, how can I combine the below 2 tables to the expected output table?
Table 1:
| Job | Order |
| J-123 | O-123 |
| J-234 | O-123 |
| J-345 | |
| J-456 | O-345 |
| J-567 | O-567 |
Table 2:
| Order | Product | Qty |
| O-123 | A | 2 |
| O-123 | B | 4 |
| O-123 | C | 5 |
| O-567 | A | 1 |
| O-567 | B | 2 |
| O-567 | C | 3 |
Expected Output:
HI Anonymous,
Perhaps you can consider adding buffer functions to your M query expression to reduce the calculation spends.
Nested functions and Table.Buffer() - Exceed
BTW, what type of data source are you worked on? If you mean the data source which supports additional statements, I'd like to suggest you enable the advanced option and using query statement to directly export the result table. They will have better performance than do these operations on the query editor side.
Power Query SQL Server connector | Microsoft Docs
Regards,Xiaoxin Sheng
8 Replies
- Ashish_Mathur
Super User
Hi Ashish_Mathur , the expected output is as shown in the screenshot below. J-345 and J-456 have no order and product.
- Ashish_Mathur
Yes, I am sorry. I missed that. I will second mahoneypat suggestion of merging in Power Query.
- mahoneypat
Microsoft Employee
From your Table 1 query, merge in Table 2 on the Order # columns (use Merge button in the ribbon), and then expand the column of "Table"s.
Pat
Hi mahoneypat , Table 2 is very large and it may affect the performance of Table 1 if I use Merge function. Is there any way to create a relationship for these 2 tables?
- mahoneypat
Yes, you can create that relationship in the diagram view. However, that will create a many-to-many relationship, which is not a good practice. Typically, a bridge table is make to connect to both tables, in your case a table with one row for each Order. This table would then connect 1:M with both of your tables shown. However, I don't know your data and what analyses/visualizations you plan to make to know if that makes sense in your case.
Pat
