Forum Discussion
Add rows that is derived from another row based on certain condition in each row. Please help.
Hi,
Need help. I imported two -3 sheets and they have like 5 columns.
Now based on values in column#5, I want to add a new column in which each row will have a certain condition.
Eg. for below table, I want to add Column -'Score' where value in each row in Column#3 i.e. Response will predict the score value we have in Column #6. How can I add that? Problem is the conditional check is not consistent and varies from one row to next.
| Sr# | Query | Response | Details | Comments | Score |
| 1 | Please answer ques1 | Select yes or no | xxx | none | if Response#1 = 'yes' then score = 1 |
| 2 | Ques2 | Select yes or no | xxx | none | if Response#1 = 'yes' then score = 5 |
| 3 | Ques3 | Select yes or no | xxx | none | if Response#1 = 'no' then score = 25 |
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
11 Replies
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
This looks promising, Thanks BA_Pete .
Can you possibly share screenshot how and where I can add these condition statements? Also,
This is my sample table. So like in excel we can create a reference (column number like E10 or E11) but how can I make reference here?
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