Matrix Data - Consolidate Columns in to Row

[jlestervas]

Hi Team,

Would like to check if anyone has a resolution to this problem, I have a sample table below:

What I need to do is to merge the Lead and Member columns into one column and then retain unique values only, and then add the lead and member allocation based on the value of new column derived from Lead and Member column.

Sample is made from excel - but i need to do this via PowerBI - note that all fields/columns are in one the same table. 

Thank you

[RossEdwards · Accepted solution]

Right now it looks like you have 2 different tables. A lead table and a Member table, each with the first column holding a persons name.  I would recommend creating a 3rd table to hold all of your people. This would be a table with 1 row per person. You join this person table to both the LEAD and MEMBER tables.

With this table relationship set up, you can use the Person name from this newly created person table, then you bring in the lead numerical column and the member numerical column.  Set the aggregation to SUM.  The table/matrix visual can produce a total for you.

You might be wondering how to create a person table.  If you have that in one of your sources, use that first.  If not, you can reference a query inside "Transform Data" to create a new table.  You can select a single column and say "Remove Other Columns" then tell it to "Remove Duplicates".  You can also set a query so that its "Enable Load" is false and it will be temporary table for transforming.  The reason I say this is that you do the above steps separately for each query, giving you 2 distinct lists.  Then use the "append as new" function to combine the results in a new table that you will load into your model.  As long as both columns have the same name, it will work.  If not, rename them after removing duplicates.  You will likely have duplicates after appending, but you can do remove duplicates a second time.

[Ashish_Mathur]

Hi,

Try these steps:

1. Split that 4 column table into 2 tables of 2 columns each
2. In each table, rename the first column to name and second to Allocation
3. In each table, create a third column (heading as member type) and have Lead in one table and Member in another
4. Now append the two tables
5. Create your visual from this.

[Anonymous · Accepted solution]

Hi @jlestervas ,

Thanks @RossEdwards  for the quick reply and solution. I have some other ideas:

(1) My test data is the same as yours.

(2) We can create two tables.

Lead Table = SELECTCOLUMNS('Table',"Lead",[Lead],"Lead Allocation",[Lead Allocation]) 

Member Table = SELECTCOLUMNS('Table',"Member",[Member],"Member Allocation",[Member Allocation]) 

(3) We can create the model relationship.

(4) We can create a measure. 

Total = MAX('Lead Table'[Lead Allocation])+MAX('Member Table'[Member Allocation])

(5) Then the result is as follows.

Best Regards,

Neeko Tang

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

[RossEdwards · Accepted solution]

Right now it looks like you have 2 different tables. A lead table and a Member table, each with the first column holding a persons name.  I would recommend creating a 3rd table to hold all of your people. This would be a table with 1 row per person. You join this person table to both the LEAD and MEMBER tables.

With this table relationship set up, you can use the Person name from this newly created person table, then you bring in the lead numerical column and the member numerical column.  Set the aggregation to SUM.  The table/matrix visual can produce a total for you.

You might be wondering how to create a person table.  If you have that in one of your sources, use that first.  If not, you can reference a query inside "Transform Data" to create a new table.  You can select a single column and say "Remove Other Columns" then tell it to "Remove Duplicates".  You can also set a query so that its "Enable Load" is false and it will be temporary table for transforming.  The reason I say this is that you do the above steps separately for each query, giving you 2 distinct lists.  Then use the "append as new" function to combine the results in a new table that you will load into your model.  As long as both columns have the same name, it will work.  If not, rename them after removing duplicates.  You will likely have duplicates after appending, but you can do remove duplicates a second time.