Performing a Merge or linking via separate tables?

[Sara16]

Hi Team

Not sure if you can help me on this quest. . I have around 40 or so columns in my data relating to survey questions capturing sentiment of Strongly Agree, Agree, Neither agree nor Disagree and Strongly disagree. What I am hoping to do is to have one column which can filter the question (all questions) and the other capture the sentiment for that question fitlered. Hopefully this can be done relatively easy? I tried group by but didnt work - thinking maybe I need a table of some sort?

Any assistance much appreciated 

[Sara16 · Accepted solution]

Hi @ryan_mayu 

Thanks for your assistance on this one - I have managed to solve the issue by ingestiging the same data twice and linking it by unique ID to get what I required. 

Appreciate all your efforts to assist in answering this question. 

[ryan_mayu]

you are welcome

[Sara16]

[Sara16]

This might help 

[Sara16]

It sure is but this is where I am stuck - my relationships wont work

[ryan_mayu]

That's why I created the dim table. Then you will not get duplicated value and will not have many to many relationship

[Sara16]

I did create a dim table - and I cant do one to many relatonship 😞 Maybe it cant be done

[Sara16]

I have tried to create a DIM table but I dont know if this works? Is there a reference doc you can refer me to for this as I am hoping to have a slicer (for example) that looks at gender and the options to select from the slicer are Male, Female and X. I may have confused my self 😞 Any assistance would be great

[ryan_mayu]

is this what you want?

[Sara16]

Hoping to have a filter like this for the graphs (so for example you can slice the questions by gender, tenure, age etc).  However all questions and relevant answers are in two columns from transposing the data 

I dont know if i need to separate and then create a relationship? 

[ryan_mayu]

can remove duplicates solve your issue? 

or you can create a new table

distinct (Questions)

that will help you to create a dim table and use the dim table to filter

[Sara16]

Hi Ryan

Sorry one last question. The transpose of questions has worked a treat to get a graph on sentiment. However with the, is there a way I can filter sentiment questions in the column by another question within that column? (eg in the question column we capture sentiment but also demographic responses - I want to have a filter on the page where you can choose a demographic question and it will filter the sentiment questions) Do I need to separate them out? 

[ryan_mayu]

could you pls provide the sample data and the expected output?

[Sara16]

Oh my gosh Ryan! Thankyou so much! This is perfect - I was searching all the forums and didnt even think of this - I am new Pivoting in tersm of "Transforming data". Cant thankyou enough 🙂 

[ryan_mayu]

you are welcome

[Sara16]

Hi @ryan_mayu 

Thankyou for this - its sorted out one aspect (transposing - so simple!) 

Unfortunately I now have over 700 columns (they were in rows) that are survey respondents answers that wont work with any of my visualisations 😞 What I am hoping achive is to have the question column (which we sorted :)) followed by sums of the answers (so for the 700+ respondents in the columns). Hoping to achieve something like the below - is there a way to do this. 

Thanks for any assistance you can provide 

[ryan_mayu]

@Sara16 

add more one step

select question column and unpivot other columns

pls see the attachment below

[ryan_mayu]

@Sara16 

you can try this

1. use header as first row

2. transpose