Transform dataset from long to wide format

[Anonymous]

Hi dear community members!

I need help to transform a dataset from a long table format to a wide one before I can merge it with another dataset which is in wide table format.

I use KoBo toolbox to collect data from a survey I'm conducting.
In this survey a part are individual questions and I also created a roster of questions (a group of questions repeated multiple times) for the household members (age, gender, school level, etc.).

The exported data in a .xls file (or imported in Excel Power Query through their API) is split (by design) in different worksheets. The first worksheet is for the main data (the individual questions) with each row representing one survey (with its index).

Then, another worksheet is created for each roster group (in my case only one) where each repetition of the roster is a new row (with the parent index related to the index/row from the main dataset). Since not every household has the same number of members, the number of rows per survey (i.e. parent index) in the roster data is not consistent.

The main data is therefore in a wide table format meanwhile that the roster data is in long table format.

To be able to analyze the data I need to merge both worksheets in a wide table format having all household members data (roster data) on the same row as the individual data. That means that I need to add the data from each row of the roster data next to the individual data they correspond to. This also implies creating new columns with new names. Here is what I would like to achieve:

The problem I have is to transform the roster data from long table format to wide to then merge it with the individual data. Merging is not really the problem, I was able to read how to do it.

On their support they explain how to merge individual data with roster data but end up with a long table format.
They have given the advice to use a combination of formulas to transform the data from long to wide (from this page) but I hope there is a better way through Power Query.

I've found a way which is to first combine the values then split them into new columns as explained here on this forum.
As this proposed solution still implies quite a lot of individual column work I was wondering if there was a quicker way since I need to transform the whole data table (depending the case that could be quite a number of columns)?

I've prepared a sample of the data here  with both worksheets and a worksheet with the result I wish to achieve. The real dataset has more columns and obviously many more rows.

I hope that I could explain my problem clearly and would be more than happy to answer further questions if needed.
Since Power Query is new to me I would be very grateful for a quite detailed explanation.

Best regards,
Vico

[lbendlin]

Neither Power Query nor Power BI like wide tables. They prefer long tables that adhere to relational data model principles.  You may need to find a different tool for what you want to achieve, for example a VBA macro.