Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MinhPham96
Frequent Visitor

Combine tables to fill up data

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

 

2019-12-05_11-04-33.png

 

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

 

2019-12-05_11-04-23.png

 

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.  

 

2019-12-05_11-04-40.png

 

Thank you for reading.

 

Best Regards,

 

Minh Pham

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

22.png
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
MinhPham96
Frequent Visitor

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

v-shex-msft
Community Support
Community Support

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

22.png
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
MinhPham96
Frequent Visitor

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
Super User
Super User

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

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.