Forum Discussion
Creating a Matrix based on the Column Values Separated by Comma
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
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.
5 Replies
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.
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?
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.
