Forum Discussion
Add rows that is derived from another row based on certain condition in each row. Please help.
Hi Anonymous ,
Apologies if I've misunderstood your requirement, but I think you need to code your column something like this:
if [Query] = "Please answer ques1" and [Response] = "Yes" then 1 else if [Query] = "Please answer ques1" and [Response] = "No" then 999 else if [Query] = "Ques2" and [Response] = "Yes" then 5 else if [Query] = "Ques2" and [Response] = "No" then 0 ... else //your escape action e.g. null, 0 etc.Pete
Hi Anonymous ,
I'm assuming that the user can answer with numeric values for DO-05?
If so, then you just need to use regular operators to evaluate them:
else if [Question ID] = "DO-05" and [Answers] < 20 then 20 else if [Question ID] = "DO-05" and [Answers] < 40 then 10If they can answer with numeric values, but these are being converted to text in your collection system, then you'd need to do something like this:
else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20 else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10Implement one of these changes for all of your "<X" lines and it should work (the "Azure" and "UPN" lines are fine, they're just not getting evaluated due to the errors in previous condition lines.
Pete
Hi Anonymous ,
You should be able to make your first condition a catch-all on the [Answers] column then continue your conditions as they are:
if [Answers] = null then 0 ... else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20 else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10 ... ... else //escape valuePete
Hi Anonymous ,
*EDIT* Just saw that you marked answer as solution, so guess you figured it out ok 🙂
In Power Query, go to Add Column tab > Custom Column. Enter what you want the column to be called in the top box, then the calculation in the bigger box at the bottom:
Pete
Can you help me with below code specially the highlighted one, having challenge with the numeric value and range checks.(if you see I did check DO-05 twice coz we need different score for different range of values) -
if [Question ID] = "AD-01" and [Answers] = "Yes" then 1
else if [Question ID] = "AD-02" and [Answers] = "No" then 25
else if [Question ID] = "NI-01" and [Answers] = "<2" then 1
else if [Question ID] = "NI-01d" and [Answers] = "<2" then 1
else if [Question ID] = "NI-01e" and [Answers] = "<2" then 1
else if [Question ID] = "APP-01b" and [Answers] = "<6" then 30
else if [Question ID] = "APP-01b" and [Answers] = "<10" then 20
else if [Question ID] = "APP-01d" and [Answers] = "Azure" then 1
else if [Question ID] = "APP-01e" and [Answers] = "UPN" then 5
else if [Question ID] = "APP-04b" and [Answers] = "<2" then 1
else if [Question ID] = "DO-05" and [Answers] = "<20" then 20
else if [Question ID] = "DO-05" and [Answers] = "<40" then 10
else if [Question ID] = "DO-20" and [Answers] = "<30" then 10
else 0
Hi Anonymous ,
I'm assuming that the user can answer with numeric values for DO-05?
If so, then you just need to use regular operators to evaluate them:
else if [Question ID] = "DO-05" and [Answers] < 20 then 20 else if [Question ID] = "DO-05" and [Answers] < 40 then 10If they can answer with numeric values, but these are being converted to text in your collection system, then you'd need to do something like this:
else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20 else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10Implement one of these changes for all of your "<X" lines and it should work (the "Azure" and "UPN" lines are fine, they're just not getting evaluated due to the errors in previous condition lines.
Pete
BA_Pete Thanks for coming to my rescue 😀, you rock buddy.
Hi BA_Pete ,
on the above range score, my query is breaking because there are rows that have not received response and have 'null' as the value. now that we converted it expects a numeric value, it starts throwing error on those rows and all following rows. I tried checking for null and then in secnd entry converted them to numeric but it didn't help. example -
else if [QuestionID] = "DO-05" and [Answers] = "null" then 0
else if [QuestionID] = "DO-05" and Number.From([Answers]) <20 then 20
else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10
Please help.
Hi Anonymous ,
You should be able to make your first condition a catch-all on the [Answers] column then continue your conditions as they are:
if [Answers] = null then 0 ... else if [Question ID] = "DO-05" and Number.From([Answers]) < 20 then 20 else if [Question ID] = "DO-05" and Number.From([Answers]) < 40 then 10 ... ... else //escape valuePete