Conditional Column - Survey Ratings

[shermayne123]

Hi,

I'm trying to create a viz to show the percentages of survey ratings for "Strongly Agree" and "Agree" for 3 survey questions using a clustered bar chart. However I noticed that the percentages were wrong because the ratings were not classified correctly.

This was what I have done: I added a conditional column using Power Query and created a series of if, then else statements for each rating from Strongly Agree to Strongly Disagree by grouping Strongly Agree and Agree as Agree/Strongly Agree and the rest as Others for each of the 3 questions(column names). Then I unpivot the 3 question columns to get the Attribute and Values column(renamed as Question and Response). However, I noticed that except for the first question, the groupings of the second and third question were wrong in that Agree and Strongly Agree are classified as Others instead of Agree/Strongly Agree.  I can't seem to find anything wrong with the if then else statements, so really appreciate if anyone can point out my mistake.  Below is the query:

= Table.AddColumn(#"Removed Blank Rows", "Response ID", each if [XXX is Faster and Easier] = "Strongly Agree" then "Agree/Strongly Agree" else if [XXX is Faster and Easier] = "Agree" then "Agree/Strongly Agree" else if [XXX is Faster and Easier] = "Slightly Agree" then "Others" else if [XXX is Faster and Easier] = "Slightly Disagree" then "Others" else if [XXX is Faster and Easier] = "Slightly Disagree" then "Others" else if [XXX is Faster and Easier] = "Disagree" then "Others" else if [XXX is Faster and Easier] = "Strongly Disagree" then "Others" else if [#"Overall Positive Experience "] = "Strongly Agree" then "Agree/Strongly Agree" else if [#"Overall Positive Experience "] = "Agree" then "Agree/Strongly Agree" else if [#"Overall Positive Experience "] = "Slightly Agree" then "Others" else if [#"Overall Positive Experience "] = "Slightly Disagree" then "Others" else if [#"Overall Positive Experience "] = "Disagree" then "Others" else if [#"Overall Positive Experience "] = "Strongly Disagree" then "Others" else if [Recommend my Colleagues] = "Strongly Agree" then "Agree/Strongly Agree" else if [Recommend my Colleagues] = "Agree" then "Agree/Strongly Agree" else if [Recommend my Colleagues] = "Slightly Agree" then "Others" else if [Recommend my Colleagues] = "Slightly Disagree" then "Others" else if [Recommend my Colleagues] = "Disagree" then "Others" else if [Recommend my Colleagues] = "Strongly Disagree" then "Others" else "Blank")

and the screenshots for question 2 and 3 and the resulting columns(part of it is blanked off for privacy reason):

Hope I have provided enough info for any guidance you can give on this. Otherwise do let me know, thank you!

[HotChilli]

Referring to your reply to me further up.

Are the 3 new columns populated correctly?

If so, what is the desired result from the example data in the screenshot (this hasn't been defined yet - only that some results are wrong)? i.e. what should the 4th new column show.   Please show it as an example.

[shermayne123]

Hi HotChilli,

Yes the 3 conditional columns are populated correctly.  I'm expecting the final result to be in the following format after unpivoting with the responses classified correctly for each survey question i.e. Agree and Strongly Agree should be classified as Agree/Strongly Agree and the rest of the responses should be classified as Others. For example, in the following table, the Agree was classified as Others for the question Overall Positive Experience and Recommend My Colleagues which were incorrect. It should have been classified as Agree/Strongly Agree.  Hope that clarifies. Otherwise do let me know again, thanks!

[shermayne123]

Hi HotChilli,

I managed to find out the solution as I was trying out the conditional columns again. So instead of adding the unconditional column first then unpivot, I should have unpivot then add the unconditional column to reclassify the responses. Problem solved. Thanks everyone for your inputs, though you didn't directly solve my problem but your responses have somehow helped me to find the way out myself. I have new question now on the filtering will ask in a separate thread. 

[wdx223_Daniel]

try this

=Table.AddColumn(#"Removed Blank Rows", "Response ID",each {null,"Agree/Strongly Agree","Others"}{List.Skip(List.Transform({[XXX is Faster and Easier],[#"Overall Positive Experience "],[Recommend my Colleagues]},each List.PositionOf({{"Strongly Agree","Agree"},{"Slightly Agree","Slightly Disagree","Disagree","Strongly Disagree"}},_,0,(x,y)=>List.Contains(x,y))),each _=-1){0}?+1 ??0})

[shermayne123]

Hi wdx223_Daniel,

Thanks for your code.  I tried it but it did not give correct classification for all of the responses to the first question because there were 3 blank answers as the question did not apply to this group of respondents. The code correctly classified two of the responses as Others but one of them as Agree/Strongly Agree.  How should I go about amending the code to fit this type of scenario?  Below screenshot is after I unpivot the columns. You can see the errors in classification for second and third question here as well. 

[wdx223_Daniel]

i'm confused of your calculation logic.

as your formula, these three columns have different priority. so when  [XXX is Faster and Easier] is "Disagree", it will be classified to "Others".

[shermayne123]

Hi wdx223_Daniel,

The 3 questions have equal priority. As per my reply to HotChilli just now, the way to get the result I want is not to reclassify the responses(add conditional columns) before I unpivot them. I should have unpivot the 3 question columns then add the conditional column to reclassify the responses accordingly. Hope that clarifies and thanks for looking into my problem.

[shermayne123]

Hi Hotchilli,

Thanks for the advice and solutions. I'm a beginner user here so appreciate if you can give further clarity here.

The first solution seems easier but I do not know how to change the logic to test all 3 columns so I tried the second solution i.e. split the logic to add 3 conditional columns(Response ID(1), (2) and (3)) but not sure what to test for the 4th conditional column. If I use if Response ID(1) equals Agree/Strongly Agree then Agree/Strongly Agree followed by else if Response ID(2) equals Agree/Strongly Agree, else if Response ID(3) equals Agree/Strongly Agree then Agree/Strongly Agree, else Others, then as long as one of the columns contains Agree/Strongly Agree, even if the others contain Others, it wil somehow wrongly classify it as Agree/Strongly Agree and I would be back to the initial problem(see below screenshot). Hope you can further enlighten me, thank you so much!

[HotChilli]

I suspect that the 'if..then' has been written in the expectation that later comparisons will over-write the positive 'hits' from earlier comparisons (because there are 3 columns in the comparison) but it won't work that way.

I think that the [XXX is Faster and Easier] comparison tests are the only ones that do anything.

So you will have to either re-write this with some logic changes to test all 3 columns in each clause OR

you could maybe split the logic to add 3 conditional columns (1 for each column tested) and then add a further conditional column to test the results of those. That'll make it a bit easier to deal with.