Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Few ways
Selectcolumns( Table, "Payment",Table[Payment], "Payee" ,Table[Payee]) // All rows
Summarize(Table,Table[Payment], Table[Payee]) // Distinct Rows
H, try this:
1. go to the create table button on the modeling ribbon
=SUMMARIZE( Table1, Table1[Payment], Table1[Payee])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |