Forum Discussion
Merge 2 Tables matching date column and Index column
I have two tables/queries in Power BI that are already matched by an similar index column, however, both tables show daily data with different columns. I'm trying to figure out how to combined these two tables so that I have daily data for each index/item and all columns. Whenever I match on index ignoring date I'm not getting the results I want. Below is an example of what I am trying to achieve.
| TABLE 1 | |||
| Date | Index | Green | |
| 1/1/2023 | 1 | 7 | |
| 1/2/2023 | 1 | 545 | |
| 1/3/2023 | 1 | 68 | |
| 1/1/2023 | 2 | 21 | |
| 1/2/2023 | 2 | 547 | |
| 1/3/2023 | 2 | 85 | |
| 1/1/2023 | 3 | 312 | |
| 1/2/2023 | 3 | 0 | |
| 1/3/2023 | 3 | 4 | |
| TABLE 2 | |||
| Date | Index | Red | |
| 1/1/2023 | 1 | 5 | |
| 1/2/2023 | 1 | 64 | |
| 1/3/2023 | 1 | 12 | |
| 1/1/2023 | 2 | 78 | |
| 1/2/2023 | 2 | 92 | |
| 1/3/2023 | 2 | 41 | |
| 1/1/2023 | 3 | 2 | |
| 1/2/2023 | 3 | 7 | |
| 1/3/2023 | 3 | 7 | |
| GOAL: | |||
| Date | Index | Green | Red |
| 1/1/2023 | 1 | 7 | 5 |
| 1/2/2023 | 1 | 545 | 64 |
| 1/3/2023 | 1 | 68 | 12 |
| 1/1/2023 | 2 | 21 | 78 |
| 1/2/2023 | 2 | 547 | 92 |
| 1/3/2023 | 2 | 85 | 41 |
| 1/1/2023 | 3 | 312 | 2 |
| 1/2/2023 | 3 | 0 | 7 |
| 1/3/2023 | 3 | 4 | 7 |
Hi sboobar
This is easy with the Merge Queries feature in Power Query. Merge queries overview - Power Query | Microsoft Learn
Click on "Merge Queries" to open the Merge window, hold on "Ctrl" key on keyboard and select both Date and Index columns as the matching columns in two tables. Select "Full Outer" join kind in case that some row is missing in one table. Click "OK".
You will have a result similar to below. A new "Table 2" column appears.
Click the expand icon on "Table 2" column header and select only "Red" column to expand to the table. You will have the expected result then.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
5 Replies
Hello sboobar,
To achieve the goal, you need to perform a join operation between the two tables based on the "Date" and "Index" columns.
Thank you. I tried your formula below and unfortunately couldn't get it to work. JOIN did not appear when I was typing the formula.
Combined = SUMX(JOIN('TABLE 1', 'TABLE 2', "Date", "Date", "Index", "Index"), [Green] + [Red])1. Load both tables into the data model.
2. Right-click on one of the tables and select "New Measure".
3. Write a DAX formula to combine the data from both tables, for example:Combined = SUMX(JOIN('TABLE 1', 'TABLE 2', "Date", "Date", "Index", "Index"), [Green] + [Red])3. Save the measure and close the formula bar.
4. Go to the report view and create a pivot table using the "Date" and "Index" columns for Rows and the "Combined" measure for Values.
This should give you the combined data you are looking for.Let me know if you require further assistance.
Hi sboobar
This is easy with the Merge Queries feature in Power Query. Merge queries overview - Power Query | Microsoft Learn
Click on "Merge Queries" to open the Merge window, hold on "Ctrl" key on keyboard and select both Date and Index columns as the matching columns in two tables. Select "Full Outer" join kind in case that some row is missing in one table. Click "OK".
You will have a result similar to below. A new "Table 2" column appears.
Click the expand icon on "Table 2" column header and select only "Red" column to expand to the table. You will have the expected result then.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
