SOLVED
Home

How to translate data into a longitudinal database

%3CLINGO-SUB%20id%3D%22lingo-sub-469517%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20translate%20data%20into%20a%20longitudinal%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311999%22%20target%3D%22_blank%22%3E%40Ian_4-learnin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20a%20little%20help%20from%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-471751%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20translate%20data%20into%20a%20longitudinal%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-471751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20response.%20I%20imagine%20I%20need%20to%20install%20power%20query%20for%20this%20work%3F%20I%20don't%20know%20if%20you%20meant%20to%20send%20me%20that%20file.%20It%20has%20a%20new%20table%2C%20but%20doesnt%20show%20how%20you%20got%20that%20new%20table.%20I%20did%20find%20the%20Power%20Query%20feature%20and%20am%20playing%20with%20it%20now%2C%20but%20cant%20figure%20out%20how%20to%20duplicate%20what%20you%20did.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469198%22%20slang%3D%22en-US%22%3EHow%20to%20translate%20data%20into%20a%20longitudinal%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469198%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I've%20been%20stuck%20on%20this%20problem%20for%20hours.%20I'm%20trying%20to%20convert%20data%20into%20a%20longitudinal%20data-set%2C%20and%20have%20built%20an%20equation%20that%20almost%20does%20the%20trick.%20BTW%20if%20there%20is%20an%20easier%20way%20to%20do%20this%2C%20I%20would%20love%20to%20learn%20it.%20In%20the%20attached%20document%20I've%20made%203%20tables.%20Table%201%20is%20the%20data%20you%20start%20with%2C%20Table%202%20is%20the%20table%20I%20want%2C%20and%20Table%203%20is%20the%20table%20I%20have.%20I've%20highlighted%20the%20parts%20that%20are%20broken%2C%20the%20rest%20I%20have%20figured%20out%20in%20my%20actual%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20big%20problem%20is%20that%20I'm%20searching%20on%20two%20variables%2C%20person_id%20and%20quiz_year%2C%20and%20I%20cant%20figure%20out%20how%20to%20make%20excel%20search%20through%20a%20range.%20For%20example%2C%20if%20a%20period_id%20of%201%20has%203%20quiz_years%2C%20my%20formula%20will%20not%20look%20beyond%20the%20first%20quiz.%20I%20think%20I'm%20just%20rambling%20now...Please%20take%20a%20look%20at%20the%20attached%20document.%20I've%20done%20my%20best%20to%20demonstrate%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help%20or%20advice%20you%20might%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUpdate%3A%20What%20I%20really%20need%20to%20know%20is%20how%20to%20use%20match%2Findex%20to%20search%20look%20at%20two%20columns%20of%20data%2C%20person_id%20and%20quiz_year.%20I%20need%20the%20formula%20to%20get%20all%20the%20way%20down%20to%20the%20correct%20quiz_year%20and%20THEN%20begin%20checking%20for%20the%20correct%20person_id.%20My%20order%20of%20operations%20is%20wrong%20and%20I%20dont%20know%20how%20to%20fix%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-469198%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-472469%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20translate%20data%20into%20a%20longitudinal%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311999%22%20target%3D%22_blank%22%3E%40Ian_4-learnin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELoad%20Table1%20into%20the%20PQ%20editor.%3C%2FP%3E%3CP%3ESelect%20columns%20person_id%20and%20quiz_year.%20Right%20click%20-%26gt%3B%20unpivot%20other%20columns.%3C%2FP%3E%3CP%3ESelect%20column%20Attribut.%20Right%20click%20-%26gt%3B%20replace%20value%20-%26gt%3B%20enter%20quiz_reponse%20and%20qr.%3C%2FP%3E%3CP%3ESelect%20column%20quiz_year.%20Click%20on%20the%20ABC%20icon%20in%20the%20header%20and%20change%20the%20data%20type%20into%20Text.%3C%2FP%3E%3CP%3EAdd%20user%20defined%20column.%20Type%20this%20formula%3A%26nbsp%3B%22year_%22%26amp%3B%5Bquiz_year%5D%26amp%3B%22_%22%26amp%3B%5BAttribut%5D%3C%2FP%3E%3CP%3ESelect%20columns%20quiz_year%20and%20Attribut.%20Right%20click%20-%26gt%3B%20Remove.%3C%2FP%3E%3CP%3ESelect%20column%20Benutzerdefiniert.%20Transform%20-%26gt%3B%20Pivot%20column.%20Select%20Value%20column%3A%20Wert.%3C%2FP%3E%3CP%3ESelect%20more%20options%3A%20Do%20not%20aggregate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20wording%20might%20differ%20because%20I%20use%20a%20non-english%20version%20and%20tried%20to%20translate.%3C%2FP%3E%3CP%3EBesides%20%22right%20click%22%20the%20commands%20are%20also%20available%20in%20the%20ribbon.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-473171%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20translate%20data%20into%20a%20longitudinal%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-473171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3EThank%20you%20for%20your%20help!%20This%20works%20just%20fine.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-473238%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20translate%20data%20into%20a%20longitudinal%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-473238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311999%22%20target%3D%22_blank%22%3E%40Ian_4-learnin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGlad%20to%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ian_4-learnin
Occasional Contributor

Hello! I've been stuck on this problem for hours. I'm trying to convert data into a longitudinal data-set, and have built an equation that almost does the trick. BTW if there is an easier way to do this, I would love to learn it. In the attached document I've made 3 tables. Table 1 is the data you start with, Table 2 is the table I want, and Table 3 is the table I have. I've highlighted the parts that are broken, the rest I have figured out in my actual file.

 

My big problem is that I'm searching on two variables, person_id and quiz_year, and I cant figure out how to make excel search through a range. For example, if a period_id of 1 has 3 quiz_years, my formula will not look beyond the first quiz. I think I'm just rambling now...Please take a look at the attached document. I've done my best to demonstrate the problem.

 

Thank you for any help or advice you might have.

 

Update: What I really need to know is how to use match/index to search look at two columns of data, person_id and quiz_year. I need the formula to get all the way down to the correct quiz_year and THEN begin checking for the correct person_id. My order of operations is wrong and I dont know how to fix it.

5 Replies

@Detlef Lewin 

 

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.

Solution

@Ian_4-learnin 

 

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies