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
mb0307
Responsive Resident
Responsive Resident

Combine two tables based on Current Month

Hi I have two tables:

 

Table1

Month Year  |  Sales
Jan-201000
Feb-202000
Mar-20500

 

 

Table2

Month Year  |  Projections
Jan-20500
Feb-202000
Mar-201000
Apr-205000

 

Let's say MARCH is current month, I want a measure to provide result so that all previous months are taken from Table1 and current month and onwards data comes from Table2, example below:

 

RESULT

Month Year  |  Projections
Jan-201000
Feb-202000
Mar-201000
Apr-205000

 

Thanks for your help.

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

Hi @mb0307 , 

Sorry for my late reply.

You could use SUMMARIZE() to select columns when there are different number of columns between tables :

Table 3 =
VAR t1 =
    FILTER (
        SUMMARIZE ( 'Previous', [  Sales], Previous[Month Year  ] ),
        MONTH ( Previous[Month Year  ] ) < 3
    )
VAR t2 =
    FILTER (
        SUMMARIZE ( 'After', [  Projections], 'After'[Month Year ] ),
        MONTH ( 'After'[Month Year ] ) >= 3
    )
RETURN
    UNION ( t1, t2 )

Then the new table will be like this:

8.12.fo.PNG

Did I answer your question ? Please mark my reply as solution.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

View solution in original post

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @mb0307 , 

Sorry for my late reply.

You could use SUMMARIZE() to select columns when there are different number of columns between tables :

Table 3 =
VAR t1 =
    FILTER (
        SUMMARIZE ( 'Previous', [  Sales], Previous[Month Year  ] ),
        MONTH ( Previous[Month Year  ] ) < 3
    )
VAR t2 =
    FILTER (
        SUMMARIZE ( 'After', [  Projections], 'After'[Month Year ] ),
        MONTH ( 'After'[Month Year ] ) >= 3
    )
RETURN
    UNION ( t1, t2 )

Then the new table will be like this:

8.12.fo.PNG

Did I answer your question ? Please mark my reply as solution.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

v-eqin-msft
Community Support
Community Support

Hi @mb0307 ,

According to my understanding, you want to combine two tables based on current month-year , right?

You could use the following formula:

 

combine =
UNION (
    FILTER ( 'Previous', 'Previous'[Month Year  ].[MonthNo] < 3 ),
    FILTER ( 'After', 'After'[Month Year ].[MonthNo] >= 3 )
)

My visualization looks like this:

8.12.5.png

Best Regards,

Eyelyn Qin

@v-eqin-msft   Thanks for you response but I am getting this error: "Each table argument of 'UNION' must have the same number of columns."

 

My two tables have different set of columns.  I want Sales and Projections union resulted in a same column please.

 

Thanks

mb0307
Responsive Resident
Responsive Resident

Any help with this query will be much appreciated.  

 

Thanks.

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.