creating a merged table from two tables where there may be zero or more than matches in each table

[ozpeacenik]

First table is a record of people mailed in two streams of a campaign.  Second is donations made (we are a charity).

I am trying to create a summary table with one record per constituent totalling the Mail cost and the revenue across the two streams.  I can do this easily except that there are some records in the second table that are not present in the first table, so when I use summarizecolumn to create a table, I am not getting an entry for those items.

I would like a table with a row per unique consistent and a sum of gifts and sum of Mail cost for every record.  

Thanks for reading my post and for any help you can give me.

[vivran22]

Hi,

Is it possible to share the excel file with the sample data. It would help in creating the solution.

Thanks,
Vivek

[MattAllington · Accepted solution]

Power BI is not like SQL. You need (read should) create a table containing a unique list of everyone you contacted. Then load a table of every communication and another table of all donations ( basically the 2 tables you already have). Join the 2 you have to the new table on ID, then use the id from the master table in your reports. 

https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

[ozpeacenik]

I can do that in my CRM.  I was hoping for something more elegant, but that would be easy enough to do.  Thanks for the suggestion.

[MattAllington]

Data modelling is the art of taking raw data and preparing it for reporting and analytics. Power BI is one of the best tools available to do this, but you have to do the work - it is not automatic.