Create a new table that contains data from an existing table

[dharrison]

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.

[v-jingzhang]

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.

[amitchandak]

@dharrison ,

Few ways

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

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

[samdthompson]

H, try this:

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

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