matrix table - column become row and calculate sum

[nardtmo]

Hi all,

hope things are well. Need help on the issue below.  I have a lot of columns in this report and so i sure if i can use unpivot. 

appreciate all the help. Be safe everyone.  

Thanks in advance

[AllisonKennedy]

Do you need the info in columns 1 to 59 for something else? Do the columns Final 1 to Final 9 change names or ever change? If you just want to unpivot those columns, you could select only columns Final 1 to Final 9 using Unpivot Only Selected Columns as an option. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[nardtmo]

Thanks. columns names for final 1 - 9 never change. but their values are changing based on the input from other table  (excel). col 1-59 is needed for tracking purposes  (feedback from all dept.).  ill try to duplicate query, remove all columns excepts the ones i need to unpivot.

[AllisonKennedy]

Add an index first, then reference the query rather than duplicate and that way you can create a relationship between the two tables after they're unpivoted. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amitchandak]

@nardtmo , In the matrix you have option show on row.  That will put the measure on the first column. You can try that

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

Others are unpivot and transpose

https://radacad.com/pivot-and-unpivot-with-power-bi

https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[mahoneypat · Accepted solution]

In the query editor, after you remove the column with "Column 1 to 59", just highlight the address through the section columns, then right click and choose unpivot other columns.  You can then have a simple measure of Sum(Table[Value}) (or whatever you rename it to). and then use the Attribute column as your rows in a matrix visual with the RF Review Summary as your columns (and the Sum() measure in the values area).

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat