Forum Discussion

sboobar's avatar
sboobar
Frequent Visitor
3 years ago
Solved

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 ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

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's avatar
sboobar
Frequent Visitor
3 years ago

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