Forum Discussion

dharrison's avatar
dharrison
New Member
6 years ago

Create a new table that contains data from an existing table

I have a table the containes data across 6 columns which looks similar to this:

Table 1 = [payment 1], [payee 1], [payment 2], [payee 2], [payment 3], [payee 3]

 

The table has multiple rows where the payment is associated with the payee.  The payment and payee can be different for each row and some rows may only have payment 1 and payee 1 or any combination of payment/payee.  

 

I need a table where payment and associated payee are stacked in a single [payment] column and a single [payee] column like this:

New Table = [Payment], [Payee]

 

I need this so I can set up a report to show all payments and their associated payees.

Need Help

3 Replies

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    6 years ago

    H, try this:

     

    1. go to the create table button on the modeling ribbon

     

    =SUMMARIZE( Table1, Table1[Payment], Table1[Payee])

     

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    dharrison ,

    Few ways

    Selectcolumns( Table, "Payment",Table[Payment], "Payee" ,Table[Payee]) // All rows

     

    Summarize(Table,Table[Payment], Table[Payee]) // Distinct Rows

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi dharrison 

    Assume your Table 1 is like:

    You can create a new table with below measure which has removed blank rows.

    Table 2 =
FILTER (
    UNION (
        SELECTCOLUMNS ( 'Table 1', "payment", [payment1], "payee", [payee1] ),
        SELECTCOLUMNS ( 'Table 1', "payment", [payment2], "payee", [payee2] ),
        SELECTCOLUMNS ( 'Table 1', "payment", [payment3], "payee", [payee3] )
    ),
    NOT ( ISBLANK ( [payment] ) )
)

    And the result is like below, is this what you want?

     

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it more quickly.