Create a new table from three existing tables (one of them has to be transposed I guess)

[Anonymous]

Hi, 

I have 3 tables :

- Idtable : ID numbers for donators

- Paymts : donations from each donator, each donation is associated to a mailing. 

- Mailings : full list of all the mailings and ID numbers who received the mailing.  

I'd like to combine in such a way that I get :  

 

Any ideas? 

Thanks in advance!!! 

[vishnurk]

Go to query editor and select the payments table.

Select the  mailing column and choose pivot option and select teh value as donation column.

[MFelix]

Hi @Anonymous,

Just add a relationhship between IDNum table and the other two tables and add the values to a matrix visual:

Rows - IDNum

Column - Mailing

Values - Donaition

Turn off the grandtotal column and rows.

Regards,

MFelix

[Anonymous]

Hi MFelix, thank you for your fast response! 

That's what I did at first, but the table contains more than 500.000 donators, more than 1000 mailings, which makes the matrix very difficult to read. Also, I'd like to compute a series of things based on that matrix. For this reason I thought it would be easier with a real table. Do you think it's possible to do that? 

[Anonymous]

Also, the matrix doesn't allow me to differenciate someone who received the mailing AND didn't donate from someone who didn't received the mailing

[MFelix · Accepted solution]

Hi @Anonymous,

The answer from @vishnurk is partial correct, if you do the pivot of the columns on the paiments you will not get the full result, especially those that don't have values on payments table.

Do the following:

1.  Merge NumberID with Mailings by IDNum
2. Expand Fied Mailings
3. Merge on the previous result the talbe Payments by IDNum and Mailing (CTRL to select more than 1 column)
4. Expand Field Donation
5. Pivot column Mailing

Then you will get result below:

M Code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IDNum = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDNum", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"IDNum"},Mailings,{"IDNum"},"Mailings",JoinKind.LeftOuter),
    #"Expanded Mailings" = Table.ExpandTableColumn(#"Merged Queries", "Mailings", {"Mailing"}, {"Mailings.Mailing"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Mailings",{"IDNum", "Mailings.Mailing"},Paymtd,{"IDNum", "Mailing"},"Paymtd",JoinKind.LeftOuter),
    #"Expanded Paymtd" = Table.ExpandTableColumn(#"Merged Queries1", "Paymtd", {"Donation"}, {"Paymtd.Donation"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Paymtd", List.Distinct(#"Expanded Paymtd"[Mailings.Mailing]), "Mailings.Mailing", "Paymtd.Donation", List.Sum)
in
    #"Pivoted Column"

See attach PBIX file

Just as a heads up don't believe that this is the optimal setup for this type of information especcially for big data as yours, but without further explanations and insights is difficult to point you to a better answer.

Regards,

MFelix