Making a table with sums from two other tables

[Anonymous]

Hello,

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)

Entity	CP/FI	Issuer	Amount	Maturity
A	CP	T	$     19,000,000	6/28/2022
B	CP	AMCR	$     10,000,000	7/1/2022
C	FI	WMT	$     25,500,000	7/1/2022
D	CP	T	$       8,000,000	7/7/2022

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

Entity	CP/FI	Issuer	Coupon Payment	Coupon Date	Coupon?
A	FI	GS	$                10,000	6/28/2022	Coupon
A	FI	F	$                15,000	7/1/2022	Coupon
B	FI	GOOG	$                12,000	7/8/2022	Coupon
B	FI	META	$                  5,000	7/9/2022	Coupon

Here is the desired result not drilled down

Date	Amount
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

Date	Amount	Entity	Issuer	Type
6/28/2022	$               10,000	A	GS	Coupon
6/28/2022	$       19,000,000	A	T	CP
7/1/2022	$               15,000	A	F	Coupon
7/1/2022	$       10,000,000	B	AMCR	CP
7/8/2022	$       25,500,000	C	WMT	FI
7/8/2022	$               12,000	B	GOOG	Coupon
7/9/2022	$                 5,000	B	META	Coupon
7/9/2022	$         8,000,000	D	T	CP

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

[Anonymous]

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

[amitchandak]

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

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

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

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

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

[amitchandak]

@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