Filtering across columns

[vcmoffatt]

Hi,

In the example below I want to filter those Customers that have a Discovery and Project Work. I want to filter out those Customers that do not have a Discovery. So for the example below I would like to remove rows 4 and 5. Is this possible, if so can you please tell me how? I have tried numerous approaches but none succesfully.

Many thanks

Number	Customer	Project	Project Type
1	A	1111	Discovery
2	A	1112	Project Work
3	A	1113	Project Work
4	B	2222	Project Work
5	C	3333	Project Work
6	D	4444	Discovery
7	D	4441	Project Work

[MarcelBeug · Accepted solution]

In the Query Editor (Power Query) it can be done as follows.

Steps:

1. Sort on Customer then on Project Type, so "Discovery"  will sort before "Project Work".
2. Add 2 indices, 1 starting with 1 and 1 with 0 so you can merge the table with itself in such a way that you have your previous customer on the same row as the current customer.
3. Sort again on Index as the merge could have disrupted the sort.
4. Add a column "Keep" with true if the Project Type is "Discovery", false if it is a new customer, otherwise null.
5. Filldown the values in the column, so you get trues in all rows for customers with a project type " Discovery".
6. Select row with "Keep"  = true.
7. Remove columns that are no longer needed.

Generated code, starting with the first step after your last step (which I called PreviousStep, so adjust with the name of your last step):

    #"Sorted Rows" = Table.Sort(PreviousStep,{{"Customer", Order.Ascending}, {"Project Type", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Customer"}, {"Previous.Customer"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Keep", each if [Project Type] = "Discovery" then true else (if [Customer] <> [Previous.Customer] then false else null)),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Keep"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Keep] = true)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1", "Previous.Customer", "Keep"})
in
    #"Removed Columns1"

n 

[vcmoffatt]

Thank you, took me a while to get my head round it, but that works.

Victoria