Select/filter ROWS between identifer

[jilbobagins]

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!

 

[Bob Phillips]

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.

 

[cyborgski]

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.

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"

 

[jilbobagins]

You sir are a gent!! Many many thanks

 

[jilbobagins]

Thank you both !!

 

[jilbobagins]

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"

 

[jilbobagins]

Forget that, having a blond moment!