Combining rows of data (separate columns) in one row

Copper Contributor

Hello everyone,

 

I have a big data sheet (multiple thousands of rows), which is an export-file from a questionnaire. We are setting up PowerBI to work on this in the future, but we're not there yet.

The problem is that the export is vertically alligned. So each answer, or label calculation, has a separate row. Thus, each unique user has 25-30 rows (depending on how far they got in the questionnaire).

 

This row contains several columns. The most important ones are the unique Client ID and the answer (or calculation) of the question. In order to do first data analysis I used 'VERT.ZOEKEN' (search vertically) to create different columns for each answer I want to analyse. So, I now have a spreadsheet with still many thousands of rows, and with 25 columns. Each row now contains 1 answer that is placed under the corresponding column. I can do calculations per column. 

 

To extra analysis, I need to ensure that each Unique User corresponds to ONE row only, which contains all answers. In the attached file I gave an example. 'Blad 1' is an example how the data now looks. Column A contains the Unique User ID, columns B, C and further contain answers.

What I need is a sheet that looks like 'Blad 2'. The different rows per user are squashed together into one row. There is always one row per answer, however, the number of rows per user can differ.

 

Can somebody help me with this?

 

Thanks in advance!

5 Replies

@PeWe76 This seems to be something for PowerQuery in Excel or inPower BI. But you didn't upload a file, though you refer to examples in Blad 1 and Blad2.

@Riny_van_Eekelen I know Power BI would be an option here. We are installing it at this moment and setting it up correctly, but time is not on my side, so I try to find a quicker solution.

 

If you could take a look at the file I attached, maybe you can see an 'easy' solution?

 

Thanks in advance.

@PeWe76 

With Power Query you may unpivot other than ID columns and after that pivot on questions.  You may check steps in attached file.

@Sergei Baklan ; it seems to work!! Thank you very much.

@PeWe76 , you are welcome