Transpond multiple columns with identical values as rows

Question

Hi all,&nbsp;I am having an issue with the format of my table with answers from a survey. See data at the bottom in table 2.&nbsp;I would like to have it formatted as shows in table1 in a visual or actual table, do you have any tips or trics in PowerQuery I can use?&nbsp;Would love to hear form you.&nbsp;Please let me know if you need more information.&nbsp;Regards,J&nbsp;Tabel1&nbsp;Totally agreeSomewhat agreeNeutralSomewhat disagreeTotally disagreeQuestion130247Question203243Question337227Question430223Question570400Question600247Question734240Question830240Question937247Question1070747Question1130247Question1230247&nbsp;Table2Question1Question2Question3Question4Question5Totally agreeTotally agreeSomewhat agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeTotally agreeTotally disagreeTotally agreeSomewhat disagreeTotally disagreeNeutralSomewhat agreeTotally agreeNeutralSomewhat agreeSomewhat agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeTotally agreeTotally agreeSomewhat agreeSomewhat agreeTotally agreeTotally disagreeNeutralTotally disagreeSomewhat disagreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeSomewhat agreeNeutralNeutralTotally agreeTotally agreeTotally agreeSomewhat agreeSomewhat agreeTotally agreeTotally disagreeTotally disagreeTotally disagreeTotally disagreeTotally disagreeTotally agreeSomewhat agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeSomewhat agreeTotally agreeSomewhat disagreeNeutralSomewhat agreeNeutralNeutralTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeTotally agreeSomewhat agreeTotally agreeSomewhat agreeSomewhat agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeSomewhat agreeSomewhat disagreeTotally agreeSomewhat disagreeNeutralSomewhat disagreeNeutralSomewhat agreeSomewhat agreeTotally agreeSomewhat disagreeNeutralSomewhat agreeTotally disagreeTotally disagreeTotally disagreeTotally disagreeTotally disagreeTotally agreeTotally agreeTotally agreeTotally agreeTotally agreeSomewhat agreeTotally agreeNeutralSomewhat agreeTotally agreeSomewhat agreeSomewhat agreeTotally disagreeSomewhat disagreeSomewhat agreeSomewhat agreeTotally agreeTotally agreeSomewhat agreeSomewhat agree

hotchilli · Accepted Answer

Add an Index (via the interface). This identifies the rows otherwise that detail will get lost in any transformations.
Then select the index column and 'Unpivot other columns'
This should be a good storage format.&nbsp; Close and Apply.
--
You can visualise this in a matrix with Question on the Rows, Value in Columns, Count of Value in Values.
I think that should do it. Let me know how it goes.

Forum Discussion

Transpond multiple columns with identical values as rows

2 Replies

Related content

Workspace Identity

Identity Insert On Warehouse table

Authentication to Sharepoint via Workspace Identity

Security of PowerBI - Fixed identity + RLS

Managed Identities Security

	Totally agree	Somewhat agree	Neutral	Somewhat disagree	Totally disagree
Question1	3	0	2	4	7
Question2	0	3	2	4	3
Question3	3	7	2	2	7
Question4	3	0	2	2	3
Question5	7	0	4	0	0
Question6	0	0	2	4	7
Question7	3	4	2	4	0
Question8	3	0	2	4	0
Question9	3	7	2	4	7
Question10	7	0	7	4	7
Question11	3	0	2	4	7
Question12	3	0	2	4	7