Results 1 to 7 of 7

Thread: Select/filter ROWS between identifer

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    31
    Articles
    0
    Excel Version
    2016

    Select/filter ROWS between identifer



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Lazy post (although I will update later with attachments!!)

    I have a query in which I need to extract rows from a table, in this example say 1 column, I want a filter that gives me all the rows that appear in-between 'SECTION 2' and 'SECTION 3'.

    Any ideas, im sure there's a simple way to do this!

    Click image for larger version. 

Name:	Capture5.JPG 
Views:	13 
Size:	56.0 KB 
ID:	9361

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Add a helper column with a formula like this, staring at row 2

    =IF(AND(COUNTIF($B$2:$B2,"SECTION 2")=1,COUNTIF($B$1:$B1,"SECTION 3")=0),"X","")

    then filter on X.

  3. #3
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    39
    Articles
    0
    Excel Version
    Office 365
    In PQ, add a Filter column that puts an x for SECTION 2 and a blank for SECTION 3. Fill down the Filter column. Now filter on Filter column = "x" and the Data Column <> "SECTION 2". Finally remove the Filter column.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "Filter", each if [Data] = "SECTION 2" then "x" else
    if [Data] = "SECTION 3" then "" else
    null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Filter"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Filter] = "x") and ([Data] <> "SECTION 2")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Filter"})
    in
        #"Removed Columns"
    Last edited by cyborgski; 2019-09-11 at 01:25 PM. Reason: Forgot the code start stop

  4. #4
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    31
    Articles
    0
    Excel Version
    2016
    You sir are a gent!! Many many thanks

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    31
    Articles
    0
    Excel Version
    2016
    Thank you both !!

  6. #6
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    31
    Articles
    0
    Excel Version
    2016
    Hi,

    Keeps on requesting I add a Token Comma Expected after "then" (ive made it bold)

    any ideas?



    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Filter", each if [Data] = "SECTION 2" then "x" else
    if [Data] = "SECTION 3" then "" else
    null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Filter"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Filter] = "x") and ([Data] <> "SECTION 2")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Filter"})
    in
    #"Removed Columns"

  7. #7
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    31
    Articles
    0
    Excel Version
    2016
    Forget that, having a blond moment!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •