Help with deciding how to model data

[rnehrboss]

Hi, I have an excel file from a survey.  It has two sheets (tables).  The first is coded responses to questions on a survey:

The second is a sheet of the coded text answers and questions:

Any thoughts on how to easily “tie” the questions and answers text to the first sheet?

Goal is to make a few stacked charts like these (which I made on a dataset that had the text all on one sheet instead of coded:

Thanks so much!

[AlB]

Hi @rnehrboss 

There a couple of ways to do this, depending on how you exactly want it. For instance, would it be everything in one chart that can be updated dynamically?

In any case it'd be best to unpivot both tables. Then create a table with the IDs for the tests and perhaps normalize the fact table. Before we go into further details, though, it'd be good to have:

1. a more thorough, accurate description of the requirements

2. some sample data as the one you show (in text-tabular format so that it can be copied)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Fowmy]

@rnehrboss 

If you can share the excel that you have shown in the screenshot, I will be able to share the solution.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[rnehrboss]

Hmmmm. my post didn't post yesterday.

Here is a file to the data.   The two sheets we are using are "Individuals Coded" and "Coded Variables"

https://drive.google.com/file/d/1KVubCV4UfT6UHCc4EyOgjISLiBEyj2gB/view?usp=sharing

Thanks so much for the advice @Fowmy @AlB 

Richard

[AlB]

@rnehrboss 

The link asks  to log on with a Google account. Can you remove that requirement, or share the file through another platform (Dropbox, OneDrive, tinyupload, etc.)?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rnehrboss]

@Fowmy @AlB 

Here's the dropbox link:

https://www.dropbox.com/scl/fi/1h6ehnqwrzk78mjzg6rky/survey-data.xlsx?dl=0&rlkey=l12j4sj73hlriw2wo0q...

[rnehrboss]

Bump.

Anyone able to offer advice?