cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vcmoffatt Frequent Visitor
Frequent Visitor

Filtering across columns

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

 

NumberCustomerProjectProject Type
1A1111Discovery
2A1112Project Work
3A1113Project Work
4B2222Project Work
5C3333Project Work
6D4444Discovery
7D4441Project Work

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filtering across columns

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"

Specializing in Power Query Formula Language (M)
2 REPLIES 2
Highlighted
Super User
Super User

Re: Filtering across columns

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"

Specializing in Power Query Formula Language (M)
vcmoffatt Frequent Visitor
Frequent Visitor

Re: Filtering across columns

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

 

Victoria