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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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