none
Advanced Filtering? RRS feed

  • Question

  • I have a table like so:

    Name       ID      Level

    John         Id1       1

    Mary        Id2        2

    Peter        Id3       3

    Gail          Id4       2

    Kyle         Id5       1

    Logan      Id6       2

    Dale        Id7       1

    What i am trying to achive is via a parametized value filter the table so that if I type in Id1, I get the following results showing:

    Name       ID      Level

    John         Id1       1

    Mary        Id2        2

    Peter        Id3       3

    Gail          Id4       2

    i.e. picks up all rows starting from Id1 upto but excluding the next row with the same level number.

    So if I input Id 5 I would get :

    Name       ID      Level

    Kyle         Id5       1

    Logan      Id6       2

    Or as another example. if I input Id2 I would get back:

    Name       ID      Level

    Mary        Id2        2

    Peter        Id3       3

    Can this be done in power query/ power bi?

    Monday, December 2, 2019 1:46 PM

Answers

  • Assuming your table is sorted, you can do:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],    //your table
        id = "id4",        //the id you are selecting
        RemoveTop = Table.Skip(Source,List.PositionOf(Source[ID],id) ),
        id_level = RemoveTop [Level] {0},
        LastPosition = List.PositionOfAny(RemoveTop[Level], {id_level}, 3) {1}?,
        KeptFirst = if LastPosition=null then RemoveTop else Table.FirstN(RemoveTop,LastPosition)
    in  KeptFirst

    Or you can make it a function:

    (id) => let
        Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],    //your table
        RemoveTop = Table.Skip(Source,List.PositionOf(Source[ID],id) ),
        id_level = RemoveTop [Level] {0},
        LastPosition = List.PositionOfAny(RemoveTop[Level], {id_level}, 3) {1}?,
        KeptFirst = if LastPosition=null then RemoveTop else Table.FirstN(RemoveTop,LastPosition)
    in  KeptFirst

    Monday, December 2, 2019 2:58 PM

All replies

  • Assuming your table is sorted, you can do:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],    //your table
        id = "id4",        //the id you are selecting
        RemoveTop = Table.Skip(Source,List.PositionOf(Source[ID],id) ),
        id_level = RemoveTop [Level] {0},
        LastPosition = List.PositionOfAny(RemoveTop[Level], {id_level}, 3) {1}?,
        KeptFirst = if LastPosition=null then RemoveTop else Table.FirstN(RemoveTop,LastPosition)
    in  KeptFirst

    Or you can make it a function:

    (id) => let
        Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],    //your table
        RemoveTop = Table.Skip(Source,List.PositionOf(Source[ID],id) ),
        id_level = RemoveTop [Level] {0},
        LastPosition = List.PositionOfAny(RemoveTop[Level], {id_level}, 3) {1}?,
        KeptFirst = if LastPosition=null then RemoveTop else Table.FirstN(RemoveTop,LastPosition)
    in  KeptFirst

    Monday, December 2, 2019 2:58 PM
  •     LastPosition = List.PositionOfAny(RemoveTop[Level], {id_level}, 3) {1}?,

    Hi Anthony

    Not sure I understand the reason of 3 (vs 2) in the above step. Could you explain please?

    Tuesday, December 3, 2019 8:46 AM
  • Very good question:
    The documentation (below) was not clear enough for me to understand and I had no time to dig in.
    I expected to use occurence =1 (to get the 2nd occurence of the level), but it did not work well. Occurence=2 seemed not working everytime, and occurence=3 was working well, so I choose 3, which gives a list of all occurences where I could pick up the 2nd item.
    There is likely space for improvement here.


    List.PositionOfAny

    Syntax

    List.PositionOfAny(
    list
    as list,
    values as list,
    optional occurrence as nullable number,
    optional equationCriteria as any
    ) as any

    About

    Returns the offset in list list of the first occurrence of a value in a list values. Returns -1 if no occurrence is found. An optional occurrence parameter occurrence can be specified.

    • occurrence: The maximum number of occurrences that can be returned.
    • Edited by anthony34 Tuesday, December 3, 2019 9:08 AM
    Tuesday, December 3, 2019 8:56 AM
  • (know where to find functions' doc - Thanks anyway)

    As far as I understood the function, with Occurrence = 1 you would get the 1st item matching {id_level}, so {id_level} itself (not what we want here)

    This Occurence option is sometimes interesting...:

    let
        Source = {1..5}
    in
        List.PositionOfAny(Source, {6}, 1)

    As expected you get -1

    Change the 1 for 2, 3... and you no longer get -1 but an empty list


    Tuesday, December 3, 2019 9:36 AM
  • (know where to find functions' doc - Thanks anyway

    Of course you know, but not everybody knows. Just trying to help here.

    And thanks for the explanation

    Tuesday, December 3, 2019 9:59 AM