Convert survey data with multiple rows per question into 1 row per response

[lowerch1]

Hello,

This is my first time using the forum, so apologies in advance for any faux pas.

I have some survey data of about 1,000 interactions with over 7,000 rows of data as each question & answer occupies its own row. In other words, one interaction gives me 7 rows of data.

What I need is for each interaction to be laid out in one row. So the data for 1 interaction appears on 1 row with the column headings being the questions & answers with the other information, such as who did we speak to, only appearing once.

I have attached an example of how I have the data now & how I would need it to be.

Thanks,

Chris

 

[Chihiro]

See attached for examples.

Answers (2). Condensed your table a bit and used formula to pull data.

Sheet1 - Copy of your original data converted into Table.
Sheet2 - Used PowerQuery to transform table and data loaded to Table2. Same condensed format as above.

Steps for PowerQuery.
1. Data converted to Table1
2. Connection only Query to Table 1 created and removed "Questions" & "Answer Text". Removed duplicates
3. Connection only Query to Table 1 created, filtered based on "Questions" column (repeat this step as many times as needed, based on # of Questions asked)
4. Reference created from query connection made in step 2. Merged all subsequent queries using Left Outer Join. Expanded "Answer Text" column for each and renamed columns appropriately ("Question 1" etc), and columns re-ordered.
5. Data connection renamed to Table2 and loaded on Shee2.