DAX (IF) Convert String to Integer for Survey Data

[BeWanderlustful]

Hey Everyone,

I have survey data with the following responses stored in my DB: Very Dissatisfied, Dissatisfied, Neutral, Satisfied, Very Satisfied. I need to convert them to numbers 1-5 for calculating scores.

The following formula for a calculated column doesn't throw any errors, however, it only converts "Neutral" and "Satisfied" to their corresponding number values:

Answer_NumberValue = IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Very Satisfied", "5", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Satisfied", "4", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Neutral", "3", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Dissatisfied", "2", IF(FRS_SURVEYRESULTS[AnswerText_Trimmed] = "Very Dissatisfied", "1", "")))))

 The AnswerText column has been trimmed to ensure there are no leading or trailing spaces, but no matter what I try, I can't get it to convert my data to the values that I need.

Any help would be much appreciated!!

[Greg_Deckler]

I would use SWITCH like:

Answer_NumberValue = 
SWITCH(
     FRS_SURVEYRESULTS[AnswerText_Trimmed],
     "Very Satisfied",5,
     "Satisfied",4,
     "Neutral",3,
     "Dissatisfied",2,
     "Very Dissatisfied",1
)

Much cleaner and should be easier to troubleshoot. 

I'm wondering if perhaps there is something wrong with your spaces perhaps? Did you do a clean and trim in the query editor?

[BeWanderlustful]

Sadly I'm getting the exact same result with the SWITCH statement, and I did Clean and Trim the data in the Power Query...

I also attempted a Conditional Column in Power Query, and STILL got the same result (only Satisfied and Neutral displaying their corresponding numbers)

I have no idea what the issue is... Especially when not even "Dissatisfied" will convert - at least if that converted I would think there was some weird spacing between the words.

Any other suggestions?

[Greg_Deckler]

First, sanity check. Create an Enter Data query and hand type your values. Save this. Add a column and paste in your SWITCH statement. Make sure it works. If it works, then we will have to look deeper.

[BeWanderlustful]

Hand creating a table with the Satisfaction values checks out - the Switch statement works without issue.

Still of course doesn't work on the actual data.

I attempted something additional. I used a SUBSTITUTE statement to replace the 5th valule in the AnswerText_Trimmed column with "", to ensure it wasn't a spacing issue. Of course it replaces the 5th value in every row "" (nothing) - however, after doing this and adjusting the SWITCH statement, it still doesn't work properly.

[Anonymous]

Have you tried creating a calculated column that returns the length of the field? Maybe if you do a len() to ensure the number of characters you're expecting are in there

[Greg_Deckler]

So here's a weird idea. Can you do a find and replace (5 of them for each one) in your query? Just replace the value with the value. I know it seems strange but just curious if that might get around your issue. Any chance you can post your data or your PBIX file?

[BeWanderlustful · Accepted solution]

Greg,

I'm not sure if this is what you intended, but upon right-clicking an individual value (Very Satisfied) in the Power Query, I realized I could individually Replace Values. I had tried doing this previously by selecting the entire column, and it didn't work (it would not find and replace the values). This time it did work, and I was able to change "Very#(00A0)Satisfied" to "VerySatisfied", and "Very#(00A0)Dissatisfied" to "VeryDissatisfied". Also, despite the column AnswerText_Trimmed having been trimmed and cleaned, "Dissatisfied" was still in fact "Dissatisfied#(00A0)". So I replaced that without the nonbreaking space.

With that said, the Switch formula now works perfectly. I have no idea how the column was not properly trimmed, or why the formula wouldn't work when I tried the Unicode characters "#(00A0)" in the actual formula. But it now works.

Which of your solutions would you prefer I mark as the answer? I'm betting my IF statement would work with this now fixed, so how about the solution for replacing the values individually?

Thanks,

- Nick

[Greg_Deckler]

I'd say that you solved it so that's the solution! I wonder if you could select the entire column and replace #(00A0) with "" and if that would solve your issue perhaps? Not sure you can do that from the interface. You might be able to go into Advanced Editor in the Query Editor and see how it is structuring the replacement and then convert that to do the entire column where you replace that #(00A0) with nothing.

And yes, your IF statement should work perfectly fine, but you don't want to do it that way. SWITCH is the way to go.

[BeWanderlustful]

Thanks for the credit, I'll mark my own answer as the answer, but I wouldn't have got there without you.

I'll check out those other options - it is certainly some very strange behaviour. And thanks for the tip about SWITCH. While I'm not new to BI tools, I am new to PowerBI, so I learned a few new things today!

Thanks again!

[Anonymous]

Was just about to post this! Including the additional spaces between the words. Just ran a test list through power bi via excel and had no issues with the switch statement.