Forum Discussion
How to translate data into a longitudinal database
Load Table1 into the PQ editor.
Select columns person_id and quiz_year. Right click -> unpivot other columns.
Select column Attribut. Right click -> replace value -> enter quiz_reponse and qr.
Select column quiz_year. Click on the ABC icon in the header and change the data type into Text.
Add user defined column. Type this formula: "year_"&[quiz_year]&"_"&[Attribut]
Select columns quiz_year and Attribut. Right click -> Remove.
Select column Benutzerdefiniert. Transform -> Pivot column. Select Value column: Wert.
Select more options: Do not aggregate.
The wording might differ because I use a non-english version and tried to translate.
Besides "right click" the commands are also available in the ribbon.
Thanks for the response. I imagine I need to install power query for this work? I don't know if you meant to send me that file. It has a new table, but doesnt show how you got that new table. I did find the Power Query feature and am playing with it now, but cant figure out how to duplicate what you did.
Load Table1 into the PQ editor.
Select columns person_id and quiz_year. Right click -> unpivot other columns.
Select column Attribut. Right click -> replace value -> enter quiz_reponse and qr.
Select column quiz_year. Click on the ABC icon in the header and change the data type into Text.
Add user defined column. Type this formula: "year_"&[quiz_year]&"_"&[Attribut]
Select columns quiz_year and Attribut. Right click -> Remove.
Select column Benutzerdefiniert. Transform -> Pivot column. Select Value column: Wert.
Select more options: Do not aggregate.
The wording might differ because I use a non-english version and tried to translate.
Besides "right click" the commands are also available in the ribbon.
Detlef_LewinThank you for your help! This works just fine.