locked
Advanced Filtering RRS feed

  • Question

  • Hi !

    I have a table that currently looks like the following. For ease of explaining, I am using only 5 columns for ease but there could be up to 20 columns.

    Col1 Col2 Col3 Col4 Col5
    Sam 280 Ok a b
    Sam 300 Ok o j
    Vick 320 NotOk c d
    Vick 340 NotOk g h
    Dave 360 NotOk g h
    Dave 380 Ok o j
    Dave 400 NotOk o j
    John 420 Ok a b
    John 440 NotOk c d
    John 460 NotOk g h
    John 480 Ok o j
    John 500 NotOk a b

    I would like to filter the above based on multiple conditions where (Col4=a and Col5=b) or (Col4=c and Col5=d). There could be many more multiple conditions like this. But I would like to avoid having to write the above for each one of the conditions resulting in a huge conditions list. How could this part be made dynamic?

    Also how would the logic be adapted in case there are additional columns added with multiple other conditions , for example: (Col4=a and Col5=b and Col6=y and Col=z) or (Col4=c and Col5=d and Col6=p and Col=q).

    Thanks
    Sam


    Monday, July 29, 2019 6:11 PM

Answers

  • Based purely on the stated requirements, and no additional assumptions, one approach would be the following:

    Create a table of criteria, like the following:

    I named the table FilterList.

    After importing into Power Query, I changed the code:

    let
        Source = Excel.CurrentWorkbook(){[Name="FilterList"]}[Content],
        toRows = Table.ToRows(Source)
    in
        toRows

    The code in your main table:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        filteredRows = Table.SelectRows(
            Source, (row) => 
            List.Accumulate(
                List.Skip(FilterList), 
                Record.Field(row, FilterList{0}{0}) = FilterList{0}{1},
                (accum, curr) => accum and Record.Field(row, curr{0}) = curr{1}
            )
        )
    in
        filteredRows

    In your original table, the second condition is redundant in all cases. If Col4 = a, Col5 will always be b. If Col4 = o, Col5 will always be j, and so on. 

    • Marked as answer by SamNaik Friday, August 2, 2019 11:07 PM
    Tuesday, July 30, 2019 11:31 AM
  • Hi Sam,

    As far as I understand, you have "permanent" filter, which should be apllied to many other steps (perhaps, also filtering ones). Then, as an option, you may try such technique:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        func = (tbl as table)=> Table.SelectRows(tbl, each ([Col4]="a" and [Col5]="b") or ([Col4]="c" and [Col5]="d")),
        filter = Table.SelectRows(func(Source), each [Col1] = "John" and [Col3] = "Ok")
    in
        filter
    • Marked as answer by SamNaik Friday, August 2, 2019 11:07 PM
    Monday, July 29, 2019 8:44 PM

All replies

  • Hi Sam,

    As far as I understand, you have "permanent" filter, which should be apllied to many other steps (perhaps, also filtering ones). Then, as an option, you may try such technique:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        func = (tbl as table)=> Table.SelectRows(tbl, each ([Col4]="a" and [Col5]="b") or ([Col4]="c" and [Col5]="d")),
        filter = Table.SelectRows(func(Source), each [Col1] = "John" and [Col3] = "Ok")
    in
        filter
    • Marked as answer by SamNaik Friday, August 2, 2019 11:07 PM
    Monday, July 29, 2019 8:44 PM
  • Thanks for the above Aleksei.
    But is there any way I could have the conditions as part of separate table or even two lists and add the name of the table or list to the SelectRows or something like that?
    I ask because I will have about 15 conditions and would like to avoid writing this below line for example 15 times for the 15 conditions.
    [Col4]="a" and [Col5]="b")
    Also in the future if I would like to include more columns as part of the filter, I would just like to update the aformentioned table or list. Is such solution possible by any chance?

    Thanks!
    Sam
    • Edited by SamNaik Monday, July 29, 2019 9:09 PM
    Monday, July 29, 2019 9:09 PM
  • maybe this thread can show you the direction you are looking for: creating a list including a function

    Tuesday, July 30, 2019 8:11 AM
  • I guess, there is misunderstanding here. You wish separate table/lists to keep filter conditions. But is it convenient way? You need to keep not only column names and values, but also signs (=, <>, >, < etc.) and logical operators (and, or). On the other hand, using method, I provided above, it seems, you achieve the goal - filter is set just once (as a function) in the usual form, then you may apply it to other steps multiple times. If you will decide to change filter conditions later - you need to change just one step.
    Tuesday, July 30, 2019 10:36 AM
  • Based purely on the stated requirements, and no additional assumptions, one approach would be the following:

    Create a table of criteria, like the following:

    I named the table FilterList.

    After importing into Power Query, I changed the code:

    let
        Source = Excel.CurrentWorkbook(){[Name="FilterList"]}[Content],
        toRows = Table.ToRows(Source)
    in
        toRows

    The code in your main table:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        filteredRows = Table.SelectRows(
            Source, (row) => 
            List.Accumulate(
                List.Skip(FilterList), 
                Record.Field(row, FilterList{0}{0}) = FilterList{0}{1},
                (accum, curr) => accum and Record.Field(row, curr{0}) = curr{1}
            )
        )
    in
        filteredRows

    In your original table, the second condition is redundant in all cases. If Col4 = a, Col5 will always be b. If Col4 = o, Col5 will always be j, and so on. 

    • Marked as answer by SamNaik Friday, August 2, 2019 11:07 PM
    Tuesday, July 30, 2019 11:31 AM