Merge 2 Tables matching date column and Index column

[sboobar]

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

[v-jingzhang · Accepted solution]

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.

[Sahir_Maharaj]

Hello @sboobar,

To achieve the goal, you need to perform a join operation between the two tables based on the "Date" and "Index" columns.

[sboobar]

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])

[Sahir_Maharaj]

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.

[Sahir_Maharaj]

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])