cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.  

Q1.JPG

 

 

 

 

 

 

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

 

 

 Q2.JPG

 

Any ideas? 

Thanks in advance!!! 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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:

~sssss.png

 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




5 REPLIES 5
Highlighted
Super User
Super User

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

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.

 

table.png

 

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Anonymous
Not applicable

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

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
Not applicable

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

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

vishnurk Frequent Visitor
Frequent Visitor

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

Go to query editor and select the payments table.

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

 

 

 

Super User
Super User

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

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:

~sssss.png

 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!