Filter rows on multiple conditions with different values

[UAA0031SL]

Hi everyone,

I've been trying to figure out a problem, looked at dozens of related questions, but wasn't able to find the right solution yet.

In the displayed Excel table, I have simplified my data set.

The task 'A' has been split into two stages and each client always has both tasks for every stage, with a status of either Open or Done.

Now, I want to create a list that shows me all of the client numbers which are in a specific phase, based on conditions of the tasks. For example: I want all the clients for which the task A1 is Done but A4 is still Open. In my example it's only client no 300. I will call this phase 'Ongoing'. If I wanted to know all the clients in the phase 'Complete', I would want the client numbers for which both A1 and A4 have the status 'Done' (in my example client no 100).

I've tried filtering in all sorts of ways, but couldn't get there. How can I make this happen?

Thank you so much in advance, I really appreciate any help I can get.

Regards,

[jdbuchanan71 · Accepted solution]

We can merge the status and employee columns into one with a delimiter (I like to use the pipe | ).

Then pivot on task name, split by delimiter and finally rename the columns to end up with this.

I have attached my sample file for you to look at the steps in the query editor.

[UAA0031SL]

Hi @jdbuchanan71,

I think this will work perfectly! Thank you so much for you help!!

Regards

[jdbuchanan71]

@UAA0031SL 

How many tasks do you have in your real data?  I am struggling with thinking of a clean way to filter by multiple task statuses where each task has a user except maybe a table for each task that has Client ID, Task Status, and User.

[UAA0031SL]

For this usecase, I will need to look at the status of 3 tasks to determine in which phase the clients are.

So eventually I will have the following lists with clients where:

- A1 still Open

- A1 Done, but A4 Open

- A4 Done

I can get behind your idea to create tables for each task, but then how will I come to the A1 - A4 list? By comparing them through hierarchy based on client number?

If there is a way to create the columns 'A1 employee' and 'A4 employee' and get the correct data in, that would be good too, because it will keep my amount of tables low and all the information regarding a client in one specific row.

[UAA0031SL]

Hi @jdbuchanan71,

Thank you very much for your reply!

Yesterday I tried applying your suggested solution. Creating a pivot column worked and I did indeed show the status values for each task stage.

However, due to the unique values in other columns, the list will show individual client numbers multiple times. I will try to figure out if this causes a problem or that I can fix this by using other smart filter options.

I will let you know if your solution worked!

Regards

[jdbuchanan71]

 The cleanest way I can think to do this is to use PowerQuery to pivot the [Task stasus] column so you have a column for each task name.  Then you can apply a slicer to the different columns.

[UAA0031SL]

Hi @jdbuchanan71,

I had a better look at applying your suggestion. Unfortunately I'm presented with a problem, because of another column. In my example I left this column out, because I didn't think it would have an effect on this situation, but it does when I apply your suggestion.

As you can see in the example below, there is also a column 'Task employee' and the values are different for each task. When I pivot the Task column, it then gives me multiple rows with null values, because it isn't able to merge due to the Task employee values differentiating.

Is there a way to solve this?

My guess is I could create columns such as 'A1 employee' and 'A4 employee', but if the task column is already pivotted, how would I get the correct data in these columns?

Thank you very much again for helping out.

Regards