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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Not applicable

Making a table with sums from two other tables



I would like to create a table that shows the total from two other tables for a specific date.  Additionally, I would like the option to drill down to see the components of that total (e.g. is it CP, FI, or a coupon?).  



Here is an example of one table (Table 1)

ACPT $     19,000,0006/28/2022
BCPAMCR $     10,000,0007/1/2022
CFIWMT $     25,500,0007/1/2022
DCPT $       8,000,0007/7/2022


Here is an example of the other table (Table 2).  

EntityCP/FIIssuerCoupon PaymentCoupon DateCoupon?
AFIGS $                10,0006/28/2022Coupon
AFIF $                15,0007/1/2022Coupon
BFIGOOG $                12,0007/8/2022Coupon
BFIMETA $                  5,0007/9/2022Coupon


Here is the desired result not drilled down

6/28/2022 $       19,010,000
7/1/2022 $       10,015,000
7/8/2022 $       25,512,000
7/9/2022 $         8,005,000


here is the desired Result Drilled down

6/28/2022 $               10,000AGSCoupon
6/28/2022 $       19,000,000ATCP
7/1/2022 $               15,000AFCoupon
7/1/2022 $       10,000,000BAMCRCP
7/8/2022 $       25,500,000CWMTFI
7/8/2022 $               12,000BGOOGCoupon
7/9/2022 $                 5,000BMETACoupon
7/9/2022 $         8,000,000DTCP


Is this possible?  Thank you in advance for your help!

Not applicable

@amitchandak I can't use UNION because the tables have different numbers of columns with different data in them.

@Anonymous , You can use append in power query and delete duplicates

Append :


or you can use selectcolumns or summarize to get the columns you need

Not applicable

Can you be more specific as to how that's done with selectcolumns or summarize?  I cannot use append as Table 2 is already a generated table

Super User
Super User

@Anonymous , You need to create common date , entity , issuer , CP/FI, type etc


example common entity 

entity = distinct(Union(distinct(Table1[entity]),distinct(Table2[entity]) ) )


Date tbale you can create like

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])



Join with both tables and use these table for slicer and in visual as common group bys

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.