locked
Find first occurrence of value in two columns and delete all the rows above it RRS feed

  • Question

  • Hi experts

    I have the table below where I would like to search for the first occurrence of two values in two separate columns and delete all rows above it.
    For example, here is a table with 3 columns : "ID", "Check if ok", "Check if team". I would like to check for first occurrence of "ok" and "yes" together (3rd row below) and delete the two rows above it.

    ID | Check if ok | Check if team
    12 |  another     |  yes
    12 |  another     |  no
    12 |  ok             |   yes
    12 |  another     |  yes
    12 |  another     |  no
    12 |  ok       |     yes

    Thanks for your help!


    • Edited by SamNaik Sunday, April 7, 2019 3:28 AM aesthetics
    Sunday, April 7, 2019 3:27 AM

Answers

  • Similar to your previous post, with a couple of mods:

    let
        source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        selectedRowPosition = Table.PositionOf(
            source, 
            [Check if ok = "ok", Check if team = "yes"], 
            Occurrence.First, 
            {"Check if ok", "Check if team"}
        ),
        retainedRows = Table.Skip(source, selectedRowPosition)
    in
        retainedRows

    Hopefully, with the two examples thus far, you can figure out other combinations.
    • Marked as answer by SamNaik Monday, April 8, 2019 4:25 AM
    Sunday, April 7, 2019 4:45 AM
  • Different than Colin's approach...  just different :-)))

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Custom1 = Table.RemoveFirstN(Source, each ([#"Check if ok"]<>"ok")  or ([#"Check if team"]<>"yes")  )
    in
        Custom1

    or 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Custom1 = Table.RemoveFirstN(Source, each {[#"Check if ok"],[#"Check if team"]} <> {"ok","yes"}  )
    in
        Custom1

    • Proposed as answer by Colin Banfield Monday, April 8, 2019 1:47 AM
    • Marked as answer by SamNaik Monday, April 8, 2019 4:26 AM
    Sunday, April 7, 2019 9:29 PM

All replies

  • Similar to your previous post, with a couple of mods:

    let
        source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        selectedRowPosition = Table.PositionOf(
            source, 
            [Check if ok = "ok", Check if team = "yes"], 
            Occurrence.First, 
            {"Check if ok", "Check if team"}
        ),
        retainedRows = Table.Skip(source, selectedRowPosition)
    in
        retainedRows

    Hopefully, with the two examples thus far, you can figure out other combinations.
    • Marked as answer by SamNaik Monday, April 8, 2019 4:25 AM
    Sunday, April 7, 2019 4:45 AM
  • Different than Colin's approach...  just different :-)))

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Custom1 = Table.RemoveFirstN(Source, each ([#"Check if ok"]<>"ok")  or ([#"Check if team"]<>"yes")  )
    in
        Custom1

    or 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Custom1 = Table.RemoveFirstN(Source, each {[#"Check if ok"],[#"Check if team"]} <> {"ok","yes"}  )
    in
        Custom1

    • Proposed as answer by Colin Banfield Monday, April 8, 2019 1:47 AM
    • Marked as answer by SamNaik Monday, April 8, 2019 4:26 AM
    Sunday, April 7, 2019 9:29 PM
  • Hi Bill,

    "Different than Colin's approach...  just different :-)))"

    Actually better. Not using a condition in the table function was an oversight on my part.

    Also,

    Table.Skip(Source, each {[Check if ok], [Check if team]} <> {"ok", "yes"})

    Monday, April 8, 2019 2:04 AM
  • Thanks Colin and Bill for the different approaches. Was able to accomplish the task given!
    Monday, April 8, 2019 4:26 AM