Creating a Matrix based on the Column Values Separated by Comma

[emman_burgonia]

Hi all, I'm having trouble creating a matrix wherein the data from columns should be the values in the rows and get some information based on the other columns. The catch is the values are separated by comma

Here's the column where the values should be in the rows. I guess I need to unpivot the columns but the rest of the columns are being transposed. 

Here's the final output should look like:

I have also attached here the sample data Group by Accreditation.pbix

[DataInsights]

@emman_burgonia,

One approach is to split the column Accredited By into rows. After this step, create a Trim step to remove spaces.

Since the above step will result in the EM Enrolment Calculated amount being copied to each row, create a custom column in Power Query using the expression below. It divides the original amount in EM Enrolment Calculated by the number of values in Accredited By. This will enable you to sum this column in a measure and get the correct amount.

[EM Enrolment Calculated] / List.Count(Text.Split([Accredited By], ","))

For the Schools measure, you can use DISTINCTCOUNT.

[emman_burgonia]

Hi, thanks for the assistance. I'd like to ask where should I insert the new column? On the same table where I split my rows?

note that i'll also filter it further by country. Would that affect my values? 

[DataInsights · Accepted solution]

@emman_burgonia,

Yes, insert the new column in the same table where you split the rows. The value in Country will copy to the new rows after you split the column, so it should not affect the values.

[emman_burgonia]

Hi @DataInsights, how do I show the enrolment in the matrix? Note that I am also using enrolment data age for columns and I'd like to also include the EM Enrolment Calculated in there

[DataInsights]

@emman_burgonia,

Create a measure that sums the custom column created in Power Query. This new column should be used instead of [EM Enrolment Calculated].