Combining Data into Summary Table

[jeggen]

All,

I'm looking for some help on how to combine some data into a summary table. The main reason is to be able to map it all out into a calendar visualization and then click on day(s) and view key activities from those days. I am looking at combining really just four fields from 2+ tables into a summary table that includes these fields, a column with the source table listed. Three of the fields would always be the same (date, client, employee), the third would vary by table but would always just be text, and the fourth field would record the source table for the summary data which I want so I can filter by activity type.

Here might be an example of the source tables and output table:

• Contracts
  • Date
  • Client
  • Employee
  • Contract Amount (specific to this table)
• Interactions
  • Date
  • Client
  • Employee
  • Summary (specific to this table)
• Payments
  • Date
  • Client
  • Employee
  • Amounts (specifc to this table)
• Summary Table:
  • Date
  • Client
  • Employee
  • Details (Contract Amount, Summary, Amount)
  • Source Table (values would be contracts, interactions, payments,)

[parry2k]

@jeggen paste some sample data and expected output. 

[jeggen]

Here's some sample tables in Excel: Note that all of the tables have more data than I need in the summary table, so it's not just as simple as merging all the data and also need to create the "source table" column, which tells me what the entry is.

Contracts
Date	Client	Employee	Contract Amount	Product
1/1/2020	ABC Inc	Joe	$10,000	Wood
3/1/2020	XYZ Inc	Mary	$50,000	Metal
4/5/2020	LMN Ink	Sam	$5,000	Plastic
Interactions
Date	Client	Employee	Summary	Status
2/15/2020	ABC Inc	Joe	Called to thank for order	Complete
3/12/2020	LMN Inc	Sam	Called to ask about contract	Complete
5/15/2020	123 Inc	Mary	Remember to check on Contract	Pending
Payments
Date	Client	Employee	Amount
1/1/2020	ABC Inc	Joe	10,000
1/25/2020	XYZ Inc	Mary	15,000
Summary Table
Date	Client	Employee	Details	Source Table
1/1/2020	ABC Inc	Joe	10,000	Payments
1/25/2020	XYZ Inc	Mary	15,000	Payments
2/15/2020	ABC Inc	Joe	Called to thank for order	Interactions
3/12/2020	LMN Inc	Sam	Called to ask about contract	Interactions
5/15/2020	123 Inc	Mary	Remember to check on Contract	Interactions
1/1/2020	ABC Inc	Joe	$10,000	Contracts
3/1/2020	XYZ Inc	Mary	$50,000	Contracts
4/5/2020	LMN Ink	Sam	$5,000	Contracts

[parry2k · Accepted solution]

@jeggen don't know the purpose of this but here it is, add the following expression as calculated table

Summary = 
UNION (
    SELECTCOLUMNS ( Contract, "Date", Contract[Date], "Client", Contract[Client], "Employee", Contract[Employee], "Details", FORMAT ( Contract[Contract Amount], "General Number" ), "Source Table", "Contract" ),
        SELECTCOLUMNS ( Interactions, "Date", Interactions[Date], "Client", Interactions[Client], "Employee", Interactions[Employee], "Details", Interactions[Summary], "Source Table", "Interactions" ),
        SELECTCOLUMNS ( Payments, "Date", Payments[Date], "Client", Payments[Client], "Employee", Payments[Employee], "Details", FORMAT ( Payments[Amount], "General Number" ), "Source Table", "Payments" )
) 

this is where you add the above expression

I would💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[jeggen]

Purpose is to integrate a couple of different tables into a high level summary and include them all in a calendar view. This worked perfect!