Combine tables to fill up data

[MinhPham96]

Hello, currently I have two data tables, the first one represents the revenue based on Project-Role-Yearmonth. This is a sample in Excel.

The second table represents the forecast Based on Project-Yearmonth.

So I want to combine both tables to have something like this. The Role for each project should be filled up, and they are the same for each Yearmonth, the missing revenue in the future month can be blank or 0.  

Thank you for reading.

Best Regards,

Minh Pham

[MinhPham96]

Dear @v-shex-msft , Thank you for the solution 

[v-shex-msft · Accepted solution]

Hi @MinhPham96 ,

I'd like to suggest you create a new table with 'CROSSJOIN' function to expand all 'project', 'YearMonth' and 'role' combinations, then you can lookup correspond values from original tables. (I haven't found any detail role mapping from your description)

Table = 
ADDCOLUMNS (
    CROSSJOIN ( ALL ( T2[Project], T2[YearMonth] ), ALL ( T1[Role] ) ),
    "Revenue", LOOKUPVALUE (
        T1[Revenue],
        T1[Project], [Project],
        T1[YearMonth], [YearMonth],
        T1[Role], [Role]
    ),
    "Forcast", LOOKUPVALUE ( T2[Forecast], T2[Project], [Project], T2[YearMonth], [YearMonth] )
)

Regards,

Xiaoxin Sheng

[MinhPham96]

Dear @danextian , thank you for replying, but one of my table is generated by using DAX to summarize from other tables, so I cannot use it in the query editor. So I tried to do what you said and use DAX NATURALLEFTOUTERJOIN() but it did not work. 

Best Regards,

Minh Pham 

[danextian]

In the query editor, create a "unique ID" column by combining Project and Yearmonth in both tables then merge both tables using the new columns to join them.

In DAX, create a calculated column using LOOKUPVALUE(). Do this in your first table assuming that if you combine Project and Yearmonth in your second table, the resulting values will have no duplicates:

Forecast =
LOOKUPVALUE (
    Table2[Forecast],
    Table2[Project], Table1[Project],
    Table2[Yearmonth], Table1[Yearmonth]
)