Forum Discussion

HarryFries's avatar
HarryFries
New Member
3 years ago

Power query conditional column with complex conditions. Then remove rows that don't meet conditions.

I'm pulling survey results from a db where the dev who built the site will not fix an error where if the user clicks too fast, it records an answer from a previous question as the answer for the next. This gives me question results that shouldn't be possibe. 

 

I'm hoping to be able use a conditional column to remove the false results. 

 

So if column A(question) = Question Q1 AND Column B(answer) = Answer A1 OR A2 Then it can it stay. But if A = Q1  amd B= A3, then it has to go.... 

 

There are 6 questions, 

Q1 has A1 & A2 possible

Q2 has A3, A4, A5, & A6 possible

Q3 has A1 & A2 possible

Q4 has A1 & A2 possible

Q5 has A7, A8, A9, A10, A11, & A12 possible

Q6 has A13, A14, A15, A16, A17,A18 & A19 possible

Is this even possible to pull execute? 

Need Help
Tutorial Requests

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago
    NewStep = Table.RemoveMatchingRows(
                                   PreviosStepName,
                                   {false},
                                   each if _ is logical then _ 
                                        else List.Contains(
                                                           {
                                                            {"Q1",{"A1","A2"}},
                                                            {"Q2",{"A3","A4","A5"}},
                                                            {"Q3",{"A1","A2"}},
                                                            {"Q4",{"A1","A2"}},
                                                            {"Q5",{"A7","A8","A9","A10","A11","A12"}},
                                                            {"Q6",{"A13","A14","A15","A16","A17","A18","A19"}}
                                                           },
                                                           _,
                                                          (x,y)=>x{0}=y[Question] and List.Contains(x{1},y[Answer])
                                                         )
                                      )

    you can update that list to match you real data.