Forum Discussion
Conditionnal sorting of rows
Hello everyone,
I've been searching for a few days now but no answer.
I'm working on the 2024 election polls and I would like to qualify the polls in the following way:
I have numerous questions that accept 2 or more answer : biden, trump and or someone else.
The results need to be qualified in a way that I only accept rows that I have only two option possible : trump or biden.
Meaning that when I sort my table out, I check the question : it needs to be biden or trump
and when I check the next row, it needs to be the same question with the answer being trump or biden (in that order)
Bit tricky to do, but need some help. There's a picture enclosed,
I wanna do that because it would help me get a clearer result on the tendency instead of just filtering out using power query to eliminate the other candidate (shapiro, kennedy, so on and so forth).
Thank you for anyone trying to help, it has been a challenged lol
Hi FVM411 !
If I understood your question correctly, you want to keep the question_id that has both candidates, Biden and Trump, and discard those questions that have more candidates or that have only Biden or Trump.
In short the condition is that they are a question_id with candidate_name Biden and Trump.There are two options, if you have Biden, Trump and some other candidates and you want this question to be in your list you can do the following:
- Select the pivot column option with the candidate_name column selected, and set it so that the column values are the Name column. This way you will have a table with the following structure:
- You can delete the rest of the columns, filter out those rows where Joe Biden or Donald Trump have null value and you will have a table like this:
Now all you would have to do is unpivot these columns, select the question_id column and right click, then click on Unpivot other columns, and you will have your table with the question_id's that appear Joe Biden and Donald Trump.Now, if you only want the questions in which Joe Biden and Donald Trump appear, without any other candidates appearing in those questions, even if they are later eliminated, you have the following option:
- First create a conditional column in which we will have 1 if the candidate is Joe Biden or Donald Trump and 0 if it is another candidate.
- Transform it to numerical format and pivot the candidates column with the values of the numerical column (the explanation of how to pivot is in the first part of the solution), you will have a table with this structure:
Then select all columns and add them together, and filter the sum column to leave only those rows with result 2. Then delete the columns of the remaining candidates and unpin the columns of Donald Trump and Joe Biden (it is explained how to do it in the first part of the question).
With this solution you should only have questions with Joe Biden and Donald Trump, not one candidate more and not one less.
2 Replies
Hi FVM411 !
If I understood your question correctly, you want to keep the question_id that has both candidates, Biden and Trump, and discard those questions that have more candidates or that have only Biden or Trump.
In short the condition is that they are a question_id with candidate_name Biden and Trump.There are two options, if you have Biden, Trump and some other candidates and you want this question to be in your list you can do the following:
- Select the pivot column option with the candidate_name column selected, and set it so that the column values are the Name column. This way you will have a table with the following structure:
- You can delete the rest of the columns, filter out those rows where Joe Biden or Donald Trump have null value and you will have a table like this:
Now all you would have to do is unpivot these columns, select the question_id column and right click, then click on Unpivot other columns, and you will have your table with the question_id's that appear Joe Biden and Donald Trump.Now, if you only want the questions in which Joe Biden and Donald Trump appear, without any other candidates appearing in those questions, even if they are later eliminated, you have the following option:
- First create a conditional column in which we will have 1 if the candidate is Joe Biden or Donald Trump and 0 if it is another candidate.
- Transform it to numerical format and pivot the candidates column with the values of the numerical column (the explanation of how to pivot is in the first part of the solution), you will have a table with this structure:
Then select all columns and add them together, and filter the sum column to leave only those rows with result 2. Then delete the columns of the remaining candidates and unpin the columns of Donald Trump and Joe Biden (it is explained how to do it in the first part of the question).
With this solution you should only have questions with Joe Biden and Donald Trump, not one candidate more and not one less.
Thanks it works great, although i initially thought of using M to solve this stuff but indeed, very nice to do it that way
