Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
vishnurk
Frequent Visitor

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
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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

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

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.