Solved: Transpose or Upivot?

SAEDG · ‎04-17-2023

Hello everyone,

I am hoping someone can help me with transposing or unpivoting some columns.

I am basically trying to turn this:

Into this in Power Query Editor:

I've tried unpivoting a few different ways, but no luck with attaching the reponse and the commentary to the same Question and ID.

Any help would be appreciated.

Thanks!

DOLEARY85 · ‎04-18-2023

Hi,

First i'd duplicate the table,
In one table delete all of the comment columns
In the 2nd table delete all the question response columns and rename the comment columns to remove the word comment - they should match the column headings of the questions in table one.
In each table select only the columns now labed with question # and select unpivot
Select one of the tables and merge the other into it joining on the ID and qestion number
Expand the column to bring in only the value field of the other table.

Hope this helps,

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

SAEDG · ‎04-18-2023

This worked perfectly, thank you!

DOLEARY85 · ‎04-18-2023

No problem, happy to help 🙂

DOLEARY85 · ‎04-18-2023

Hi,

First i'd duplicate the table,
In one table delete all of the comment columns
In the 2nd table delete all the question response columns and rename the comment columns to remove the word comment - they should match the column headings of the questions in table one.
In each table select only the columns now labed with question # and select unpivot
Select one of the tables and merge the other into it joining on the ID and qestion number
Expand the column to bring in only the value field of the other table.

Hope this helps,

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍